We help IT Professionals succeed at work.
Get Started

FILL IN MISSING DATES

934 Views
Last Modified: 2013-12-02
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
Comment
Watch Question
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 4 Answers and 13 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE