Sql - Split Total Time By Time Intervals

I have an entry that looks like this: UserID---StatusStart---StatusEnd---StatusKey---StateDuration Joe1------8:59:46--------9:08:06-------Available-----500 What I need to do is s

Solution 1:

Old school approach, but I would create a second table:

CREATETABLE SplitTimes (
    SplitStart timenotnull,
    SplitEnd timenotnull,
    primary key (SplitStart, SplitEnd)
)

And populate it:

INSERTINTO SplitTimes (SplitStart, SplitEnd) VALUES
('0:00', '0:30'),
('0:30', '1:00'),
('1:00', '1:30'),
('1:30', '2:00'),
('2:00', '2:30'),
('2:30', '3:00'),
('3:00', '3:30'),
('3:30', '4:00'),
('4:00', '4:30'),
('4:30', '5:00'),
('5:00', '5:30'),
('5:30', '6:00'),
('6:00', '6:30'),
('6:30', '7:00'),
('7:00', '7:30'),
('7:30', '8:00'),
('8:00', '8:30'),
('8:30', '9:00'),
('9:00', '9:30'),
('9:30', '10:00'),
('10:00', '10:30'),
('10:30', '11:00'),
('11:00', '11:30'),
('11:30', '12:00'),
('12:00', '12:30'),
('12:30', '13:00'),
('13:00', '13:30'),
('13:30', '14:00'),
('14:00', '14:30'),
('14:30', '15:00'),
('15:00', '15:30'),
('15:30', '16:00'),
('16:00', '16:30'),
('16:30', '17:00'),
('17:00', '17:30'),
('17:30', '18:00'),
('18:00', '18:30'),
('18:30', '19:00'),
('19:00', '19:30'),
('19:30', '20:00'),
('20:00', '20:30'),
('20:30', '21:00'),
('21:00', '21:30'),
('21:30', '22:00'),
('22:00', '22:30'),
('22:30', '23:00'),
('23:00', '23:30'),
('23:30', '23:59:59.9999999');

Now I can run this:

SELECT e.UserID
    ,casewhen e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart endas SplitStatusStart
    ,casewhen e.StatusEnd   <= t.SplitEnd   then e.StatusEnd   else t.SplitEnd   endas SplitStatusEnd
    ,e.StatusKey
    ,datediff(second, casewhen e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end, 
        casewhen e.StatusEnd   <= t.SplitEnd   then e.StatusEnd   else t.SplitEnd   end) SplitStatusDuration
FROM EntryTable e
INNER JOIN SplitTimes t
    ON  e.StatusStart <= t.SplitEnd
    AND e.StatusEnd >= t.SplitStart

It can be made to work with datetimes instead of times and it can be made to work across midnight. It's just extra fiddling to strip the time from the date and add the other one back on.

This has the advantage of not being a recursive CTE, which may perform better on a large table.

Solution 2:

Another Option

Example

Declare@YourTableTable ([UserID] varchar(50),[StatusStart] time,[StatusEnd] time,[StatusKey] varchar(50),[StateDuration] int)
InsertInto@YourTableValues 
 ('Joe1','8:59:46','9:08:06','Available',500)
,('ZZZZ','8:59:46','10:08:06','Available',500)  -- Added multi hourSelect A.UserID
      ,StatusStart =convert(time,IIF(DatePart(HOUR,A.StatusStart)=H,StatusStart,DateAdd(HOUR,H,0)))
      ,StatusEnd   =convert(time,IIF(DatePart(HOUR,A.StatusStart)=H ,DateAdd(HOUR,H+1,0),IIF(H<DatePart(HOUR,StatusEnd),DateAdd(HOUR,H+1,0),StatusEnd)))
      ,A.StatusKey
      ,A.StateDuration 
 From@YourTable A
 Join (
        Select Top 24 H=-1+Row_Number() Over (OrderBy (SelectNULL))
         From  master..spt_values n1
      ) B on H between DatePart(HOUR,A.StatusStart) and DatePart(HOUR,A.StatusEnd)

Returns

Solution 3:

You can use a recursive CTE like this:

createtable #temp (UserID nvarchar(100), StatusStart time, StatusEnd time, StatusKey nvarchar(100), StateDuration int)
insertinto #temp (UserID, StatusStart, StatusEnd, StatusKey, StateDuration) values
    ('Joe1', '8:59:46', '9:08:06', 'Available', 500),
    ('Joe2', '8:59:46', '10:08:06', 'Available', 500)

;WITH cte AS (
    SELECT UserID, StatusStart, CONVERT(time, DATEADD(hour, DATEDIFF(hour, 0, StatusStart) +1, 0)) AS t, StatusEnd, StatusKey, StateDuration FROM #temp
    UNIONALLSELECT UserID, t, CONVERT(time, DATEADD(hour, 1, t)) AS t, StatusEnd, StatusKey, StateDuration FROM cte
    WHERE t < StatusEnd
)
SELECT UserID, StatusStart, CASEWHEN t > StatusEnd THEN StatusEnd ELSE t ENDAS t, StatusKey, StateDuration FROM cte ORDERBY UserID, t

droptable #temp

The CTE selects the start time and the next hour, then unions with itself using that last hour as the next start time, until the next hour is greater than StatusEnd. I had to add a couple of converts to time there because DATEADD returns datetimes.

Results:

Solution 4:

Here's an inline table valued function solution that utilizes a numbers table to create the time segments. I also made a sample table with your test cases to demonstrate output for each of your requests.

DROPFUNCTIONIFEXISTSitvf_Segments;
GOCREATEFUNCTIONitvf_Segments(@start TIME,@end TIME)
RETURNSTABLEASRETURN
(
    WITH N1 (Number) AS (SELECT 1 UNION ALL SELECT 1)
        ,N2 (Number) AS (SELECT 1 FROM N1 CROSS JOIN N1 AS N2)
        ,N3 (Number) AS (SELECT 1 FROM N2 CROSS JOIN N2 AS N3)
        ,N4 (Number) AS (SELECT 1 FROM N3 CROSS JOIN N3 AS N4)
        ,Numbers (Number) AS (SELECT TOP 48 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM N4)
        ,Times (StartTime,EndTime) AS (SELECT TIMEFROMPARTS(Number/2,Number%2*30,0,0,0),DATEADD(minute,30,TIMEFROMPARTS(Number/2,Number%2*30,0,0,0)) FROM Numbers)
    SELECT 
        CASE WHEN @start > T.StartTime THEN @start ELSE T.StartTime END AS NewStatusStart
        ,CASE WHEN @end < T.EndTime THEN @end ELSE T.EndTime END AS NewStatusEnd
    FROM Times AS T
    WHERE @start <= T.EndTime
        AND @end >= T.StartTime
)
GO
;

DROPTABLEIFEXISTSUserStatus;

CREATETABLEUserStatus
(
    UserID VARCHAR(10)
    ,StatusStart TIME
    ,StatusEnd TIME
    ,StatusKey VARCHAR(25)
    ,StateDuration SMALLINT
)
;

INSERTINTOUserStatus
(UserID,StatusStart,StatusEnd,StatusKey,StateDuration)
VALUES
('Joe1','08:59:46','09:08:06','Available',500)
,('Joe2','08:59:46','10:08:06','Available',500)
;

SELECT *
FROMUserStatusCROSSAPPLYitvf_Segments(StatusStart,StatusEnd)
ORDERBYUserID,NewStatusStart,NewStatusEnd
;