Question :
I am dealing with data that needs to be looked at on a shift-to-shift basis (8:00:00 to 20:00:00 and its reciprocal are the two shifts) There are instances where a timestamp (one row) will span longer than a shift. Below is an example of what I am looking for.
----------------------------------------------------------------------------------------------------------
Original Timestamp Data
----------------------------------------------------------------------------------------------------------
START_TIME END_TIME
2020-07-16 04:54:50 2020-07-27 06:36:14
----------------------------------------------------------------------------------------------------------
Updated Timestamp Data
---------------------------------------------------------------------------------------------------------
-
START_TIME END_TIME
2020-07-16 04:54:50 2020-07-16 08:00:00
2020-07-16 08:00:00 2020-07-16 20:00:00
2020-07-16 20:00:00 2020-07-17 08:00:00
2020-07-17 08:00:00 2020-07-17 20:00:00
. .
. .
. .
2020-07-26 20:00:00 2020-07-27 06:36:14
Here is the code I have tried but I am only able to split the data into two rows. SOmething tells me that the “Start Roll” and “End Roll” Columns within #T1 are not going to work in a situation like this.
Declare @DayTurn as DATETIME, @NightTurn As DATETIME, @TodaysDate As DATETIME, @DateCheck As DATETIME, @TimeChange As Integer, @MidNight As DATETIME
Set @DayTurn = '8:00:00'
Set @NightTurn = '20:00:00'
SET @TodaysDate = GETDATE()
SET @DateCheck = CASE WHEN DATEPART( WK, @TodaysDate) >= 7 THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
ELSE DATEADD(Week,-6,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
END;
SELECT
(Case
When cast(Activity.[START_TIME_UTC] as time) >= cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@NightTurn as time) and cast(Activity.[START_TIME_UTC]) as time) < cast(@NightTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), DATEADD(hour, @TimeChange, Activity.[END_TIME_UTC]) , 112) + ' ' + CONVERT(CHAR(8), @NightTurn, 108))
else CONVERT(datetime, Activity.[START_TIME_UTC]) end) as 'START_TIME'
,(Case
When cast(Activity.[START_TIME_UTC] as time) < cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC]) as time) <= cast(@NightTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@DayTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[START_TIME_UTC] , 112) + ' ' + CONVERT(CHAR(8), @DayTurn, 108))
else CONVERT(datetime, Activity.[START_TIME_UTC]) end) as 'Start Roll'
,(case
When cast(Activity.[START_TIME_UTC] as time) < cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC]) as time) <= cast(@NightTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@DayTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[START_TIME_UTC], 112) + ' ' + CONVERT(CHAR(8), @DayTurn, 108))
else CONVERT(datetime, Activity.[END_TIME_UTC]) end ) As 'END_TIME'
,(Case
When cast(Activity.[START_TIME_UTC] as time) >= cast(@DayTurn as time) and cast(Activity.[END_TIME_UTC] as time) > cast(@NightTurn as time) and cast(Activity.[START_TIME_UTC] as time) < cast(@NightTurn as time) then CONVERT(DATETIME, CONVERT(CHAR(8), Activity.[END_TIME_UTC] , 112) + ' ' + CONVERT(CHAR(8), @NightTurn, 108))
else CONVERT(datetime, Activity.[END_TIME_UTC]) end) as 'End Roll'
into #T1
FROM [MY_DATA] as Activity
Where Activity.[START_TIME_UTC] >= @DateCheck
SELECT * INTO #T2 from(
Select
temp.[START_TIME]
,temp.[END_TIME]
From #T1 as temp
UNION
Select
temp.[Start Roll]
,temp.[End Roll]
From #T1 as temp
) as temp;
SELECT
*
FROM #T2
Order By START_TIME;
Drop Table #T1
Drop Table #T2
Any and all help is greatly appreciated.
Cheers!
Answer :
I don’t know if this is the best way to do it but it works.
I’m using a reference table that contains all the shifts and and running the select against it. In this case, I used variable for the start and end timestamp but you can reproduce using your column.
declare @start as datetime
declare @end as datetime
set @start='2020-07-16 04:54:50'
set @end='2020-07-27 06:36:14'
-- You could write a dynamic SQL query to populate this table but I went with raw data for simplicity
create table #calendar (shift_start datetime, shift_end datetime)
insert into #calendar values
('2020-07-15 08:00','2020-07-15 20:00'),('2020-07-15 20:00','2020-07-16 08:00'),
('2020-07-16 08:00','2020-07-16 20:00'),('2020-07-16 20:00','2020-07-17 08:00'),
('2020-07-17 08:00','2020-07-17 20:00'),('2020-07-17 20:00','2020-07-18 08:00'),
('2020-07-18 08:00','2020-07-18 20:00'),('2020-07-18 20:00','2020-07-19 08:00'),
('2020-07-19 08:00','2020-07-19 20:00'),('2020-07-19 20:00','2020-07-20 08:00'),
('2020-07-20 08:00','2020-07-20 20:00'),('2020-07-20 20:00','2020-07-21 08:00'),
('2020-07-21 08:00','2020-07-21 20:00'),('2020-07-21 20:00','2020-07-22 08:00'),
('2020-07-22 08:00','2020-07-22 20:00'),('2020-07-22 20:00','2020-07-23 08:00'),
('2020-07-23 08:00','2020-07-23 20:00'),('2020-07-23 20:00','2020-07-24 08:00'),
('2020-07-24 08:00','2020-07-24 20:00'),('2020-07-24 20:00','2020-07-25 08:00'),
('2020-07-25 08:00','2020-07-25 20:00'),('2020-07-25 20:00','2020-07-26 08:00'),
('2020-07-26 08:00','2020-07-26 20:00'),('2020-07-26 20:00','2020-07-27 08:00'),
('2020-07-27 08:00','2020-07-27 20:00'),('2020-07-27 20:00','2020-07-28 08:00'),
('2020-07-28 08:00','2020-07-28 20:00'),('2020-07-28 20:00','2020-07-29 08:00')
--get the start and the appropriate shift end
select @start, shift_end from #calendar where @start between shift_start and shift_end
union
-- get all periode between both start and finish
select * from #calendar where shift_start > @start and shift_end < @end
union
-- get the end and its appropriate shift end
select shift_Start, @end from #calendar where @end between shift_start and shift_end;
drop table #calendar;