Split duration hourly depending on start and end time I have a table that registers the status for equipment. The status has a start and an end time. But now I want to know for every hour what the status is. If the status was "Operating" from 15:20 to 17:10, I want to see that it was 40 minutes on Operating for the 16th hour of the day, 60 minutes operating on the 17th hour and 10 minutes operating on the 18th hour of the day.

This is what I have now:


Shift_date  Status     Start_timestamp  End_Timestamp
----------  ---------  ---------------  ---------------
5/20/2017   Operating  5/20/2017 8:21   5/20/2017 10:40
5/21/2017   Delay      5/20/2017 10:40  5/20/2017 11:10
5/22/2017   Operating  5/20/2017 11:10  5/20/2017 13:50


This is what I want:


Shift_date  Hour  Status     Duration (minutes)
----------  ----  ---------  ------------------
5/20/2017   1     ..         ..
5/20/2017   ..    ..         ..
5/20/2017   9     Operating  39
5/20/2017   10    Operating  60
5/20/2017   11    Operating  40
5/20/2017   11    Delay      20
5/20/2017   12    Delay      10
5/20/2017   12    Operating  50
5/20/2017   13    Operating  50
5/20/2017   13    ..         ..
5/20/2017   14    ..         ..
5/20/2017   ..    ..         ..
5/20/2017   24    ..         ..
The basic idea here is to add rows for each row in the status table depending on how many hours it covers. I'm doing this by joining to a numbers table. From there, you just need to account for all of the different cases to figure out how many minutes for the status are relevant for each hour bucket.

Data prep:

CREATE TABLE #t (
Shift_date DATETIME NOT NULL,
[Status] VARCHAR(20) NOT NULL,
Start_timestamp DATETIME NOT NULL,
End_Timestamp DATETIME NOT NULL
);

INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 8:21', '5/20/2017 10:40');
INSERT INTO #t VALUES ('5/20/2017', 'Delay', '5/20/2017 10:40', '5/20/2017 11:10');
INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 11:10', '5/20/2017 13:50');


One approach which should work on 2008:

-- may need more rows here depending on how long the events can be
WITH numbers (n) AS ( 
    SELECT 0 
    UNION ALL SELECT 1 
    UNION ALL SELECT 2 
    UNION ALL SELECT 3 
    UNION ALL SELECT 4 
    UNION ALL SELECT 5
)
SELECT 
   t2.Shift_date
 , DATEPART(HOUR, start_final) AS [hour]
 , t2.[Status]
 , DATEDIFF(MINUTE, start_final, end_final) Duration
FROM
(
    SELECT Shift_date
    , [Status]
    , Start_timestamp
    , End_Timestamp
    , CASE WHEN t.Start_timestamp &gt; n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final
    , CASE WHEN t.End_timestamp &gt; n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final
    FROM #t t
    CROSS APPLY
    (
        SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers
        , dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbers
        FROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) &gt;= n.n
    ) n
) t2
ORDER BY start_final;


My results (it looks like you have a few issues with your sample data):

╔═════════════════════════╦══════╦═══════════╦══════════╗
║       Shift_date        ║ hour ║  Status   ║ Duration ║
╠═════════════════════════╬══════╬═══════════╬══════════╣
║ 2017-05-20 00:00:00.000 ║    8 ║ Operating ║       39 ║
║ 2017-05-20 00:00:00.000 ║    9 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Operating ║       40 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Delay     ║       20 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Delay     ║       10 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Operating ║       50 ║
║ 2017-05-20 00:00:00.000 ║   12 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   13 ║ Operating ║       50 ║
╚═════════════════════════╩══════╩═══════════╩══════════╝