By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/* Initial setup - Data table and Numbers table */
CREATE TABLE #Data (DTID integer, machineID integer, startTime datetime, endTime datetime, duration float);
INSERT INTO #Data (DTID, machineID, startTime, endTime, duration) VALUES
(1,333,'2023-09-21 11:00:00','2023-09-21 11:15:00',15.0),
(2,334,'2023-09-21 11:05:00','2023-09-21 11:10:00',5.0),
(3,333,'2023-09-21 11:17:00','2023-09-21 11:18:00',1.0),
(4,334,'2023-09-21 11:16:00','2023-09-21 11:20:00',4.0);
CREATE TABLE #Nums (n int PRIMARY KEY);
INSERT INTO #Nums (n)
SELECT A.n + B.n * 10 + C.n * 100 + D.n * 1000 + E.n * 10000
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS A(n)
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS B(n)
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS C(n)
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS D(n)
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS E(n);
100004 rows affected
/* Table with each minute of downtime (separated) */
CREATE TABLE #DT (Down_Minute smalldatetime, machineID int, PRIMARY KEY (Down_Minute, machineID));
INSERT INTO #DT (Down_Minute, machineID)
SELECT DATEADD(minute, #Nums.n, #Data.startTime) AS Down_Minute, #Data.machineID
FROM #Data
CROSS JOIN #Nums
WHERE #Nums.n < #Data.duration;
SELECT TOP 10 * FROM #DT ORDER BY Down_Minute;
| Down_Minute | machineID |
|---|---|
| 2023-09-21 11:00 | 333 |
| 2023-09-21 11:01 | 333 |
| 2023-09-21 11:02 | 333 |
| 2023-09-21 11:03 | 333 |
| 2023-09-21 11:04 | 333 |
| 2023-09-21 11:05 | 333 |
| 2023-09-21 11:05 | 334 |
| 2023-09-21 11:06 | 333 |
| 2023-09-21 11:06 | 334 |
| 2023-09-21 11:07 | 333 |
/* Report - Number of machines down each minute */
SELECT Down_Minute, COUNT(*) AS Num_Machines_Down
FROM #DT
GROUP BY Down_Minute
ORDER BY Down_Minute;
| Down_Minute | Num_Machines_Down |
|---|---|
| 2023-09-21 11:00 | 1 |
| 2023-09-21 11:01 | 1 |
| 2023-09-21 11:02 | 1 |
| 2023-09-21 11:03 | 1 |
| 2023-09-21 11:04 | 1 |
| 2023-09-21 11:05 | 2 |
| 2023-09-21 11:06 | 2 |
| 2023-09-21 11:07 | 2 |
| 2023-09-21 11:08 | 2 |
| 2023-09-21 11:09 | 2 |
| 2023-09-21 11:10 | 1 |
| 2023-09-21 11:11 | 1 |
| 2023-09-21 11:12 | 1 |
| 2023-09-21 11:13 | 1 |
| 2023-09-21 11:14 | 1 |
| 2023-09-21 11:16 | 1 |
| 2023-09-21 11:17 | 2 |
| 2023-09-21 11:18 | 1 |
| 2023-09-21 11:19 | 1 |
/* Report - Downtime by machine by minute */
SELECT p.*
FROM (SELECT Down_Minute, machineID FROM #DT) A
PIVOT (COUNT(machineID) FOR machineID IN ([333],[334])) p
/* Also store this in a table */
CREATE TABLE #DM (Down_Minute smalldatetime PRIMARY KEY, [333] int, [334] int, NewGrp_flag tinyint, GroupNum int);
INSERT INTO #DM (Down_Minute, [333], [334])
SELECT p.Down_Minute, p.[333], p.[334]
FROM (SELECT Down_Minute, machineID FROM #DT) A
PIVOT (COUNT(machineID) FOR machineID IN ([333],[334])) p;
| Down_Minute | 333 | 334 |
|---|---|---|
| 2023-09-21 11:00 | 1 | 0 |
| 2023-09-21 11:01 | 1 | 0 |
| 2023-09-21 11:02 | 1 | 0 |
| 2023-09-21 11:03 | 1 | 0 |
| 2023-09-21 11:04 | 1 | 0 |
| 2023-09-21 11:05 | 1 | 1 |
| 2023-09-21 11:06 | 1 | 1 |
| 2023-09-21 11:07 | 1 | 1 |
| 2023-09-21 11:08 | 1 | 1 |
| 2023-09-21 11:09 | 1 | 1 |
| 2023-09-21 11:10 | 1 | 0 |
| 2023-09-21 11:11 | 1 | 0 |
| 2023-09-21 11:12 | 1 | 0 |
| 2023-09-21 11:13 | 1 | 0 |
| 2023-09-21 11:14 | 1 | 0 |
| 2023-09-21 11:16 | 0 | 1 |
| 2023-09-21 11:17 | 1 | 1 |
| 2023-09-21 11:18 | 0 | 1 |
| 2023-09-21 11:19 | 0 | 1 |
/* Determine 'streaks' using 'islands and gaps' approach */
WITH DM_NewGrp AS
(SELECT Down_Minute, NewGrp_flag,
CASE WHEN LAG(Down_Minute, 1) OVER (ORDER BY Down_Minute) = DATEADD(minute, -1, Down_Minute)
AND LAG([333], 1) OVER (ORDER BY Down_Minute) = [333]
AND LAG([334], 1) OVER (ORDER BY Down_Minute) = [334]
THEN 0 ELSE 1
END AS NewGrp_flag_calc
FROM #DM
)
UPDATE DM_NewGrp
SET NewGrp_flag = NewGrp_flag_calc;
WITH DM_GrpNums AS
(SELECT Down_Minute, GroupNum,
SUM(NewGrp_flag) OVER (ORDER BY Down_Minute) AS GroupNum_calc
FROM #DM
)
UPDATE DM_GrpNums
SET GroupNum = GroupNum_calc;
SELECT MIN(Down_Minute) AS startDT, MAX(Down_Minute) AS endDT_inclusive,
[333], [334], [GroupNum]
FROM #DM
GROUP BY [333], [334], [GroupNum]
ORDER BY [GroupNum];
| startDT | endDT_inclusive | 333 | 334 | GroupNum |
|---|---|---|---|---|
| 2023-09-21 11:00 | 2023-09-21 11:04 | 1 | 0 | 1 |
| 2023-09-21 11:05 | 2023-09-21 11:09 | 1 | 1 | 2 |
| 2023-09-21 11:10 | 2023-09-21 11:14 | 1 | 0 | 3 |
| 2023-09-21 11:16 | 2023-09-21 11:16 | 0 | 1 | 4 |
| 2023-09-21 11:17 | 2023-09-21 11:17 | 1 | 1 | 5 |
| 2023-09-21 11:18 | 2023-09-21 11:19 | 0 | 1 | 6 |
/* Calculate durations down */
WITH Downtimes AS (
SELECT MIN(Down_Minute) AS startDT, MAX(Down_Minute) AS endDT_inclusive,
[333], [334], [GroupNum]
FROM #DM
GROUP BY [333], [334], [GroupNum]
)
SELECT startDT, endDT_inclusive, [333], [334],
DATEDIFF(minute, startDT, endDT_inclusive) + 1 AS Duration_down,
CASE WHEN [333]=1 AND [334]=1 THEN 999
WHEN [333] = 1 THEN 333
ELSE 334
END AS MachineID,
([333] + [334]) * (DATEDIFF(minute, startDT, endDT_inclusive) + 1) * 0.5 AS Downtime
FROM Downtimes
ORDER BY startDT;
| startDT | endDT_inclusive | 333 | 334 | Duration_down | MachineID | Downtime |
|---|---|---|---|---|---|---|
| 2023-09-21 11:00 | 2023-09-21 11:04 | 1 | 0 | 5 | 333 | 2.5 |
| 2023-09-21 11:05 | 2023-09-21 11:09 | 1 | 1 | 5 | 999 | 5.0 |
| 2023-09-21 11:10 | 2023-09-21 11:14 | 1 | 0 | 5 | 333 | 2.5 |
| 2023-09-21 11:16 | 2023-09-21 11:16 | 0 | 1 | 1 | 334 | 0.5 |
| 2023-09-21 11:17 | 2023-09-21 11:17 | 1 | 1 | 1 | 999 | 1.0 |
| 2023-09-21 11:18 | 2023-09-21 11:19 | 0 | 1 | 2 | 334 | 1.0 |