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 > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final
    , CASE WHEN t.End_timestamp > 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) >= 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 ║
╚═════════════════════════╩══════╩═══════════╩══════════╝

StackExchange Database Administrators Q#174035, answer score: 5