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


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

  
 declare @maxdt datetime = (Select Max(EndTime) From test)
 ;WITH mycte AS
(
SELECT min( StartTime ) eTime, max(detail) as detail  FROM test 
UNION ALL
SELECT DATEADD(MINUTE,30,m.eTime) eTime , detail
FROM mycte m 
WHERE  m.eTime< @maxdt  
)
 
, mycte2 as 
(select  eTime, m.detail
,ISNULL(t1.detail, t2.detail) as detail2 
,row_number()over(order by eTime) rn from mycte m
Left Join test t1 on m.etime= t1.starttime  
Left Join test t2 on m.etime= t2.endtime)

 

,mycte3 as
(
Select  d.etime as etime1,e.etime as etime2
,CASE WHEN d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END as detail
,Row_number() Over(Order BY m.etime) -  Row_number() Over(Partition By 
CASE WHEN d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END 
Order BY m.etime) rn  
FROM mycte2 m
OUTER APPLY (SELECT TOP 1 detail2, etime FROM mycte2
WHERE rn<= m.rn AND detail2 IS NOT NULL ORDER BY rn DESC) d
OUTER APPLY (SELECT TOP 1 detail2, etime FROM mycte2
WHERE rn>= m.rn AND detail2 IS NOT NULL ORDER BY rn ) e
)

 
 

,mycteFinal as (Select Cast(min(etime1) as time(0)) as starttime
, Cast( max(etime2) as time(0)) as endtime
, detail  
FROM mycte3
WHERE etime1<=@maxdt
Group by detail,rn
)

Select Convert(varchar(7),starttime,100) starttime
, Convert(varchar(7),endtime,100) endtime
, detail from mycteFinal
Order by Cast(starttime as datetime)


drop table test


/*
starttime	endtime	detail
9:30AM	10:30AM	WORK
10:30AM	11:00AM	Break 1
11:00AM	2:00PM	WORK
2:00PM	3:00PM	Lunch
3:00PM	4:30PM	WORK
4:30PM	5:00PM	Break 2
5:00PM	6:30PM	WORK

*/

 
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