Create Three Shifts for a Period of Time


  

  
  DECLARE @StartTime DATE = '20150401'
DECLARE @EndTime DATE = '20150405'

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)


Select   Dateadd(day,n-1, @StartTime ) as [Date], s as [Shift]
,dateadd(hour,6+8*(s-1), Cast( Dateadd(day,n-1, @StartTime ) as datetime)) [Start Time]
,dateadd(hour,6+8*s,  Cast(Dateadd(day,n-1, @StartTime ) as datetime))  [End Time]

FROM nums cross apply (values(1),(2),(3)) d(s)
where Dateadd(day,n-1, @StartTime ) <= @EndTime


 
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