SQL - Spilt timestamp into multiple rows

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!

• LowlyDBA - John M: A lot of similar questions have been asked before, check those first.
• BigMac: I've looked through the forums and can't find quite what I am looking for. A lot of those questions are related to timestamps that are more or less fixed. The difficulty i am having is the times that are 1. Between 8:00, 2. between 20:00 and any single timestamp that is greater than a twelve hour period (i.e. will be between multiple transition times).

Answer 1

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;