I have the below script, which basically gives a calendar for each personid for the dates between '01-JAN-2009' and previous day; but I need it changing, I want it to only show the date upto when that personid left the company and not sure how to do this, I have had a go under the orginal script but getting errors.
//Original Script
Cal_Tmp1: Load PersonId Resident People;
Join LOAD date, date as DateForKey, week, month, year, [shift 1], dow as [Day of Week], [S1 Time]; SQL SELECT convert(varchar,c.datekey,103) as date, convert(varchar,right(convert(varchar,c.datekey,103),2))+'.'+case when len(c.weeknum) = 1 Then '0'+convert(varchar,c.weeknum) else convert(varchar,c.weeknum) end as week, substring(datename(m,c.datekey),1,3)+'-'+substring(datename(yy,c.datekey),3,2) as month, CalYear as year, case when c.WeekdayNum between 1 and 5 then 1 else 0 end as [shift 1], datename(dw,c.datekey) as dow, case when c.WeekdayNum between 1 and 5 then 7.5 else 0 end as [S1 Time] From dimCalendar c where c.datekey between '01-JAN-2009' and dateadd(d,-1,getdate()) ;
//New Script
Cal_Tmp1: Load PersonId Resident People;
Join LOAD date, date as DateForKey, week, month, year, [shift 1], dow as [Day of Week], [S1 Time]; SQL SELECT convert(varchar,c.datekey,103) as date, convert(varchar,right(convert(varchar,c.datekey,103),2))+'.'+case when len(c.weeknum) = 1 Then '0'+convert(varchar,c.weeknum) else convert(varchar,c.weeknum) end as week, substring(datename(m,c.datekey),1,3)+'-'+substring(datename(yy,c.datekey),3,2) as month, CalYear as year, case when c.WeekdayNum between 1 and 5 then 1 else 0 end as [shift 1], datename(dw,c.datekey) as dow, case when c.WeekdayNum between 1 and 5 then 7.5 else 0 end as [S1 Time] From Calendar c cross join User p left join PersonLogin pl on p.personid = pl.login left join person pe on pl.userid = pe.userid
where c.datekey between '01-JAN-2009' and case when pe.terminationdate is null then dateadd(d,-1,getdate()) else pe.terminationdate end;
Have you thought about changing your strategy to include the beginning and end date in the table People and then doing a intervalmatch between the that table and the master calendar table?
Here is an example. Instead of Start and Stop you would use Start Date and End Date in your example. The time field would then be you date field from your master calendar.
