clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249262 fiddles created (32197 in the last week).

CREATE TABLE Table1 ([SID] int, [Equipment] nvarchar(1), [PreviousTime] datetime, [TranstionTime] datetime, [PreviousStatus] nvarchar(20), [Status] nvarchar(20)); INSERT INTO Table1 ([SID], [Equipment], [PreviousTime], [TranstionTime], [PreviousStatus], [Status]) VALUES (0, 'A', '2018-06-06 12:00:00', '2018-06-07 09:00:00', N'停機', N'運行'), (1, 'A', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'), (2, 'A', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'), (3, 'A', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'), (4, 'A', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'), (5, 'A', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行') , (6, 'B', '2018-06-07 09:00:00', '2018-06-07 10:00:00', N'運行', N'停機'), (7, 'B', '2018-06-07 10:00:00', '2018-06-07 12:00:00', N'停機', N'運行'), (8, 'B', '2018-06-07 12:00:00', '2018-06-07 14:00:00', N'運行', N'維修'), (9, 'B', '2018-06-07 14:00:00', '2018-06-09 10:00:00', N'維修', N'停機'), (10, 'B', '2018-06-09 10:00:00', '2018-06-09 12:00:00', N'停機', N'運行'), -- 額外加入 (11, 'C', '2018-06-06 06:00:00', '2018-06-07 09:00:00', N'停機', N'運行'), (12, 'D', '2018-06-06 06:00:00', '2018-06-07 08:00:00', N'停機', N'運行'); select * from Table1;
SID Equipment PreviousTime TranstionTime PreviousStatus Status
0 A 2018-06-06 12:00:00.000 2018-06-07 09:00:00.000 停機 運行
1 A 2018-06-07 09:00:00.000 2018-06-07 10:00:00.000 運行 停機
2 A 2018-06-07 10:00:00.000 2018-06-07 12:00:00.000 停機 運行
3 A 2018-06-07 12:00:00.000 2018-06-07 14:00:00.000 運行 維修
4 A 2018-06-07 14:00:00.000 2018-06-09 10:00:00.000 維修 停機
5 A 2018-06-09 10:00:00.000 2018-06-09 12:00:00.000 停機 運行
6 B 2018-06-07 09:00:00.000 2018-06-07 10:00:00.000 運行 停機
7 B 2018-06-07 10:00:00.000 2018-06-07 12:00:00.000 停機 運行
8 B 2018-06-07 12:00:00.000 2018-06-07 14:00:00.000 運行 維修
9 B 2018-06-07 14:00:00.000 2018-06-09 10:00:00.000 維修 停機
10 B 2018-06-09 10:00:00.000 2018-06-09 12:00:00.000 停機 運行
11 C 2018-06-06 06:00:00.000 2018-06-07 09:00:00.000 停機 運行
12 D 2018-06-06 06:00:00.000 2018-06-07 08:00:00.000 停機 運行
 hidden batch(es)


WITH CTE_X1 AS ( SELECT SID,Equipment,PreviousTime,TranstionTime, -- 時間平移 8 小時 DATEADD(HOUR, -8, PreviousTime) AS PreviousNew, DATEADD(HOUR, -8, TranstionTime) AS TranstionNew, PreviousStatus, -- 計算天數 DATEDIFF(DAY,DATEADD(HOUR, -8, PreviousTime),DATEADD(HOUR, -8, TranstionTime))+1 AS DayCount FROM ( SELECT SID,Equipment,PreviousTime,TranstionTime,PreviousStatus FROM Table1 UNION -- 合併最後一筆 SELECT SID,Equipment,TranstionTime AS PreviousTime, CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(DAY, 1, TranstionTime),120) + ' 08:00:00') AS TranstionTime, Status AS PreviousStatus FROM ( SELECT *,ROW_NUMBER() OVER (PARTITION BY Equipment ORDER BY TranstionTime DESC) AS ROWNUM FROM Table1) AS TableY WHERE ROWNUM = 1) AS TableZ), -- 依 DayCount-1> 0 遞減,日期遞增 CTE_GetDate AS ( SELECT SID,Equipment,PreviousNew,TranstionNew,PreviousStatus,PreviousTime,TranstionTime, CONVERT(DATE, PreviousNew) AS DateStart,CONVERT(DATE, DATEADD(DAY, 1, PreviousNew)) AS DateEnd, CASE WHEN TranstionNew <= CONVERT(DATE, DATEADD(DAY, 1, PreviousNew)) THEN DATEDIFF(MINUTE, PreviousNew, TranstionNew) ELSE DATEDIFF(MINUTE, PreviousNew, CONVERT(DATE, DATEADD(DAY, 1, PreviousNew))) END AS NewMinute,DayCount AS 'DayCount' FROM CTE_X1 UNION ALL SELECT SID,Equipment,PreviousNew,TranstionNew,PreviousStatus,PreviousTime,TranstionTime, DATEADD(DAY, 1, DateStart),DATEADD(DAY, 1, DateEnd), CASE WHEN TranstionNew <= CONVERT(DATE, DATEADD(DAY, 1, DateEnd)) THEN DATEDIFF(MINUTE, PreviousNew, TranstionNew) ELSE DATEDIFF(MINUTE, PreviousNew, CONVERT(DATE, DATEADD(DAY, 1, DateEnd))) END AS NewMinute,(DayCount - 1) AS 'DayCount' FROM CTE_GetDate WHERE DayCount - 1 > 0) -- SELECT DateStart AS '日期',Equipment AS '設備', SUM(CASE WHEN PreviousStatus = N'停機' THEN FinalMinute END) AS '停機時間', SUM(CASE WHEN PreviousStatus = N'運行' THEN FinalMinute END) AS '運行時間', SUM(CASE WHEN PreviousStatus = N'維修' THEN FinalMinute END) AS '維修時間', SUM(FinalMinute) AS '總共時間' FROM ( SELECT Equipment,PreviousStatus,DateStart, -- 計算時間差 NewMinute - LAG(NewMinute, 1, 0) OVER (PARTITION BY SID, Equipment, PreviousStatus ORDER BY DateStart) AS FinalMinute FROM CTE_GetDate) AS TableX WHERE FinalMinute > 0 GROUP BY DateStart, Equipment ORDER BY DateStart, Equipment
日期 設備 停機時間 運行時間 維修時間 總共時間
2018-06-05 C 120 120
2018-06-05 D 120 120
2018-06-06 A 1200 1200
2018-06-06 C 1440 1440
2018-06-06 D 1440 1440
2018-06-07 A 180 180 1080 1440
2018-06-07 B 120 180 1080 1380
2018-06-07 C 60 1380 1440
2018-06-07 D 1440 1440
2018-06-08 A 1440 1440
2018-06-08 B 1440 1440
2018-06-09 A 120 1200 120 1440
2018-06-09 B 120 1200 120 1440
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)