Daily Activity Fill Up With T-SQL (Time Interval) Part 2


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e019246-2f30-4eb6-91fd-80bbf92957a1/resolve-start-time-and-end-time-in-employee-shift?forum=transactsql

 create table test (id int identity(1,1), EmpID int, ShiftDate date, StartTime time,  EndTime time, Detail varchar(50))
insert into test values (1,'2013-11-01','9:31 AM', '6:31 PM', 'WORK')
,(1,'2013-11-01','10:39 AM', '11:09 AM', 'Break 1')
,(1,'2013-11-01','11:45 AM', '2:00 PM', 'Lunch')
,(1,'2013-11-01','4:35 PM', '5:00 PM', 'Break 2')
,(1,'2013-11-18','9:30 AM', '6:30 PM', 'WORK')
,(1,'2013-11-18','10:37 AM', '11:00 AM', 'Break 1')
,(1,'2013-11-18','2:00 PM', '3:00 PM', 'Lunch')
,(1,'2013-11-18','4:36 PM', '5:00 PM', 'Break 2')
,(2,'2013-11-18','9:30 AM', '6:30 PM', 'WORK')
,(2,'2013-11-18','10:30 AM', '11:00 AM', 'Break 1')
,(2,'2013-11-18','2:05 PM', '3:00 PM', 'Lunch')
,(2,'2013-11-18','4:31 PM', '5:00 PM', 'Break 2') 
,(3,'2013-11-15','9:30 AM', '6:30 PM', 'WORK')
,(3,'2013-11-15','10:30 AM', '12:50 pM', 'Break 1') 
,(3,'2013-11-15','4:31 PM', '5:00 PM', 'Break 2')
,(4,'2013-10-18','9:30 AM', '6:30 PM', 'WORK')
,(4,'2013-10-18','10:30 AM', '11:00 AM', 'Break 1')
,(4,'2013-10-18','2:05 PM', '3:00 PM', 'Lunch')
,(4,'2013-10-18','4:31 PM', '5:00 PM', 'Break 2') 
 
declare @maxdt time = (Select Max(EndTime) From test)
 ;WITH mycte AS
(
SELECT EmpID,ShiftDate, min( StartTime ) eTime, max(detail) as detail  FROM test 
Group by EmpID,ShiftDate
UNION ALL
SELECT EmpID, ShiftDate, DATEADD(MINUTE,1,m.eTime) eTime , detail
FROM mycte m 
WHERE  m.eTime< @maxdt  
)
------ Select * into mytestCalendar  --For optimization, you need to work with a calendar table similar as this CTE
------ from mycte
------Order by eTime
 
, mycte2 as 
(select m.EmpID, m.ShiftDate, eTime, m.detail
,ISNULL(t1.detail, t2.detail) as detail2 
,row_number()over(order by m.ShiftDate, eTime) rn from mycte m
Left Join test t1 on m.etime= t1.starttime And m.EmpID=t1.EmpID And m.ShiftDate= t1.ShiftDate
Left Join test t2 on m.etime= t2.endtime And m.EmpID=t2.EmpID And m.ShiftDate= t2.ShiftDate)
 
,mycte3 as
(
Select m.EmpID, m.ShiftDate, d.etime as etime1,e.etime as etime2
,CASE WHEN  d.EmpID=e.EmpID AND d.ShiftDate=e.ShiftDate AND d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END as detail
,Row_number() Over(Partition By  m.EmpID, m.ShiftDate Order BY m.etime) -  Row_number() Over(Partition By  m.EmpID, m.ShiftDate,
CASE WHEN  d.EmpID=e.EmpID AND d.ShiftDate=e.ShiftDate AND d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END 
Order BY  m.etime) rn  
FROM mycte2 m
OUTER APPLY (SELECT TOP 1 EmpId, ShiftDate,detail2, etime FROM mycte2
WHERE EmpID=m.EmpID AND ShiftDate=m.ShiftDate AND rn<= m.rn AND detail2 IS NOT NULL ORDER BY rn DESC) d
OUTER APPLY (SELECT TOP 1 EmpID,ShiftDate, detail2, etime FROM mycte2
WHERE EmpID=m.EmpID AND ShiftDate=m.ShiftDate AND rn>= m.rn AND detail2 IS NOT NULL ORDER BY rn ) e
)
--Select * from mycte3
--Order by EmpID, ShiftDate, eTime1
,mycteFinal as (
Select EmpID,   ShiftDate
,Cast(min(etime1) as time(0)) as starttime
,Cast( max(etime2) as time(0)) as endtime
, detail  
FROM mycte3
WHERE etime1<=@maxdt
Group by EmpID,ShiftDate, detail,rn
)
Select EmpID, ShiftDate, Convert(varchar(7),starttime,100) starttime
, Convert(varchar(7),endtime,100) endtime
, detail from mycteFinal
Order by EmpID, ShiftDate, Cast(starttime as datetime)
--the max recursion number
Option (MaxRecursion 32767)
drop table test

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s