0

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!

2
  • A lot of similar questions have been asked before, check those first. Jul 28 '20 at 15:11
  • 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).
    – BigMac
    Jul 29 '20 at 12:24
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.