troubleshooting Question

FILL IN MISSING DATES

Avatar of PHIL Sawyer
PHIL SawyerFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2008
13 Comments4 Solutions939 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 4 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros