fetch

Part 5 SQL query to find employees hired in last n months



Sharing buttons:

this is part 5 of sequences were

interview questions and answers video

series in its video we'll discuss

writing a sequel query to find all the

employees who are hired in the last 10

months if you have used the inbuilt

date dev sequence of a function then you

already know the answer we'll be using

this employee's table for this demo

let's flip the sequence of management

studio I've already created this

employees table it is the sequel script

that can do it

I'll have the script available on my

blog in case you need it let's look at

writing the sequel query itself we want

to select all the columns from employees

table along with the all columns I also

want to select the difference between

today's date and the date an employee is

hired in months and the easiest way to

achieve that is by using date day

function this method expects three

parameters the first parameter is the

interval that is how do you want the

difference between dates to be reported

do you want the difference in days

months or years we want it in months so

I'm going to pass month as the first

parameter the second parameter is the

start date the start date is going to be

hire date so we want the difference

between hire date and today's date how

are we going to get today's date we can

use get date inbuilt function and let's

give this column an early let's call it

death and then let's order the results

by hire date in descending order let's

execute this query and see what output

we get so look at the odd but notice the

first two rows if you look at these two

employees they're hired in the month of

May and if you look at today's date it's

May 30th so we are in the same month so

obviously the difference between their

hire date and today's date in months is

0 but for the employees who are hired in

the month of April the difference in

months is 1 for the employees who are

hired in the month of March the

difference is 2 so now we can use this

expression to filter and get the

employees that we want let's say for

example the interviewer has asked you to

find all the employees who are hired

the last three months all you need to do

is use a very loss so where the day

difference in months is between 1 and 3

so that will give us the employees who

are hired in the month of April March

and February so let's execute this query

look at that so people who are hired in

the month of April March and February of

2014 are only retrieved ok so very

straightforward now let's say for

example if the interviewer asks you can

you write a query to find all the

employees who are hired in the last 30

days then we can use the same query and

then by simply changing the interval

parameter here we can achieve that so we

want to find out the employees who are

hired in the last 30 days so we want the

difference between hire date and today's

date in days and if that's the case use

day as the parameter value for date they

function and then let's change this

upper threshold level to 30 so let's

execute this and look at this we are

only getting the employees you know who

are hired in the last 30 days ok now if

the interviewer asks you you know can

you write a query to find the employees

who are hired in the last one year then

simply replace this day with year

so the lower threshold is going to be

one upper threshold is going to be one

okay so when we execute this we get all

the employees who are hired in the 2013

okay look at that people who are hired

from December through January of 2013

are only returned if you want you know

all the employees who are hired in the

last two years okay that is in 2014 as

well as 2013 change the lower threshold

to zero now do we really need to have

this date day function in the Select

list not really since we want to

visually see the difference you know in

the results that we have included it

there but the actual query need not have

it so here you can put you know any

number that you want and that's going to

give you you know the employees who are

hired until that year so it's that

simple that's it for today thank you for

listening

have a great day