SQL – Spilt timestamp into multiple rows

Posted on

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;

Leave a Reply

Your email address will not be published.