Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Shift Date

Author  Topic 

samir.first
Starting Member

34 Posts

Posted - 2013-12-09 : 07:41:01
DECLARE @startDate DATETIME = '2013-12-09 08:00:00' , @EndDate DATETIME = '2013-12-11 20:00:00'

ShiftOn = 8
ShiftTow = 4
ShiftThree = 12
I need Table Contain
DateTimeFrom DateTimeTO Shift
2013-12-09 08:00 2013-12-09 12:00 ShiftOne
2013-12-09 12:00 2013-12-09 16:00 ShiftTow
2013-12-09 16:00 2013-12-09 24:00 ShiftThree
2013-12-10 08:00 2013-12-10 12:00 ShiftOne
2013-12-10 12:00 2013-12-10 16:00 ShiftTow
2013-12-10 16:00 2013-12-10 24:00 ShiftThree
... etc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-09 : 07:46:06
maybe you mean 4,4,8 and not 8,4,12?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 09:06:48
[code]
DECLARE @startDate DATETIME = '2013-12-09 08:00:00' , @EndDate DATETIME = '2013-12-11 20:00:00'

DECLARE @ShiftOn int= 8
DECLARE @ShiftTow int = 4
DECLARE @ShiftThree int = 12



;With CTE
AS
(
SELECT DATEADD(hh,@ShiftOn,DATEADD(dd,DATEDIFF(dd,0,@startDate),0)) AS DateVal,1 AS Cnt
UNION ALL
SELECT DATEADD(hh,CASE WHEN Cnt % 3 = 0 THEN @ShiftOn WHEN Cnt % 3 = 1 THEN @ShiftTow WHEN Cnt % 3 = 2 THEN @ShiftThree ELSE 0 END,DateVal),Cnt + 1
FROM CTE
WHERE DATEADD(hh,CASE WHEN Cnt % 3 = 0 THEN @ShiftOn WHEN Cnt % 3 = 1 THEN @ShiftTow WHEN Cnt % 3 = 2 THEN @ShiftThree ELSE 0 END,DateVal) <= @EndDate
)
SELECT c1.DateVal,c2.DateVal
FROM CTE c1
JOIN CTE c2
ON c2.Cnt = c1.Cnt + 1
AND DATEPART(hh,c1.DateVal ) >= @ShiftOn

output
-------------------------------------------------
DateVal DateVal
-------------------------------------------------
2013-12-09 08:00:00.000 2013-12-09 12:00:00.000
2013-12-09 12:00:00.000 2013-12-10 00:00:00.000
2013-12-10 08:00:00.000 2013-12-10 12:00:00.000
2013-12-10 12:00:00.000 2013-12-11 00:00:00.000
2013-12-11 08:00:00.000 2013-12-11 12:00:00.000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -