Hello
I have created a table of dates (daily) and then joined to my data where it returns employees and their start date in a given department and End Date of Employment. Note: An employee could move departments and then have a new start date
eg
#DATES Department Start Date End Date Emp_ID
10/01/2013 A 10/01/2013 13/01/2013 1234
11/01/2013 NULL NULL NULL
12/01/2013 NULL NULL NULL
13/01/2013 NULL NULL NULL
14/01/2013 NULL NULL NULL
etc etc
What I would like is ..
#DATES Department Start Date End Date Emp_ID
10/01/2013 A 10/01/2013 13/01/2013 1234
11/01/2013 A 10/01/2013 13/01/2013 1234
12/01/2013 A 10/01/2013 13/01/2013 1234
13/01/2013 A 10/01/2013 13/01/2013 1234
14/01/2013 NULL NULL NULL
My sql so far would be like ..
create table #dates
(
CustomMonth char(6),
lookupdate DATETIME
)
declare @date_increment DATETIME
--change start date here
set @date_increment = '2000-12-31'
--change end date here
while @date_increment < '2013-12-31'
begin
set @date_increment = DATEADD(day,1,@date_increment)
insert #dates (CustomMonth, lookupdate)
select convert(char(4),(datepart(year,@date_increment)))
+ RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, @date_increment)), 2),
@date_increment
end
select
T1.lookupdate ,
MYDATA.DEPARTMENT,
MYDATA.START_DATE,
MYDATA.EMP_ID
FROM #dates T1
LEFT JOIN
(
SELECT
EMP_ID,
DEPARTMENT,
START_DATE,
END_DATE
FROM MYTABLE
)SD ON T1.lookupdate = SD.START_DATE
ORDER BY 1,2,3
drop table #dates
Regards
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.