Shift Date - SQL Server Forums

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources