SQL - Split total time by time intervals

Question

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 split this into two entries. One that shows the stateduration from 8:59:46 to 9:00:00 and a second that's from 9:00:00 until the end of 9:08:06

Ultimately I'm hoping to be able to have something that I can apply to a huge table of this information so I can break out what and how much time is spent each half hour for the agents. I'm overthinking this, I'm positive I am because I spin myself into a web each time - there has to be something simple I'm forgetting.

Also, ideally whatever formula it is, it would work if the time spanned over multiple intervals. Such as the StatusStart being 8:59:46 but the StatusEnd being 10:08:06.

Answer 1

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.

DROP FUNCTION IF EXISTS itvf_Segments;
GO

CREATE FUNCTION itvf_Segments(@start TIME,@end TIME)
RETURNS TABLE
AS
RETURN
(
    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
;

DROP TABLE IF EXISTS UserStatus;

CREATE TABLE UserStatus
(
    UserID VARCHAR(10)
    ,StatusStart TIME
    ,StatusEnd TIME
    ,StatusKey VARCHAR(25)
    ,StateDuration SMALLINT
)
;

INSERT INTO UserStatus
(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 *
FROM UserStatus
    CROSS APPLY itvf_Segments(StatusStart,StatusEnd)
ORDER BY UserID,NewStatusStart,NewStatusEnd
;

Answer 2

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

CREATE TABLE SplitTimes (
    SplitStart time not null,
    SplitEnd time not null,
    primary key (SplitStart, SplitEnd)
)

And populate it:

INSERT INTO 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
    ,case when e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end as SplitStatusStart
    ,case when e.StatusEnd   <= t.SplitEnd   then e.StatusEnd   else t.SplitEnd   end as SplitStatusEnd
    ,e.StatusKey
    ,datediff(second, case when e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end, 
        case when 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.

Answer 3

You can use a recursive CTE like this:

create table #temp (UserID nvarchar(100), StatusStart time, StatusEnd time, StatusKey nvarchar(100), StateDuration int)
insert into #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
    UNION ALL
    SELECT UserID, t, CONVERT(time, DATEADD(hour, 1, t)) AS t, StatusEnd, StatusKey, StateDuration FROM cte
    WHERE t < StatusEnd
)
SELECT UserID, StatusStart, CASE WHEN t > StatusEnd THEN StatusEnd ELSE t END AS t, StatusKey, StateDuration FROM cte ORDER BY UserID, t

drop table #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:

Answer 4

Another Option

Example

Declare @YourTable Table ([UserID] varchar(50),[StatusStart] time,[StatusEnd] time,[StatusKey] varchar(50),[StateDuration] int)
Insert Into @YourTable Values 
 ('Joe1','8:59:46','9:08:06','Available',500)
,('ZZZZ','8:59:46','10:08:06','Available',500)  -- Added multi hour


Select 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 (Order By (Select NULL))
         From  master..spt_values n1
      ) B on H between DatePart(HOUR,A.StatusStart) and DatePart(HOUR,A.StatusEnd)

Returns