DECLARE @Temp TABLE ( SID INT, Equipment NVARCHAR(10), PreviousTime DATETIME, TranstionTime DATETIME, PreviousStatus NVARCHAR(10), Status NVARCHAR(10) ) INSERT INTO @Temp (SID,Equipment,PreviousTime,TranstionTime,PreviousStatus,Status) VALUES (0 ,'A','2018/6/6 12:00:00','2018/6/7 09:00:00',N'停機',N'運行'), (1 ,'A','2018/6/7 09:00:00','2018/6/7 10:00:00',N'運行',N'停機'), (2 ,'A','2018/6/7 10:00:00','2018/6/7 12:00:00',N'停機',N'運行'), (3 ,'A','2018/6/7 12:00:00','2018/6/7 14:00:00',N'運行',N'維修'), (4 ,'A','2018/6/7 14:00:00','2018/6/9 10:00:00',N'維修',N'停機'), (5 ,'A','2018/6/9 10:00:00','2018/6/9 12:00:00',N'停機',N'運行'), (6 ,'B','2018/6/7 09:00:00','2018/6/7 10:00:00',N'運行',N'停機'), (7 ,'B','2018/6/7 10:00:00','2018/6/7 12:00:00',N'停機',N'運行'), (8 ,'B','2018/6/7 12:00:00','2018/6/7 14:00:00',N'運行',N'維修'), (9 ,'B','2018/6/7 14:00:00','2018/6/9 10:00:00',N'維修',N'停機'), (10 ,'B','2018/6/9 10:00:00','2018/6/9 12:00:00',N'停機',N'運行') DECLARE @SDATE DATETIME DECLARE @EDATE DATETIME --搜尋條件，以天為單位 SET @SDATE='2018-06-06' SET @EDATE='2018-06-09' --一天從早上8:00開始 SET @SDATE=@SDATE+'08:00' SET @EDATE=@EDATE+'08:00' --用CTE將開始到結束時間拆開成每天 ;WITH DATE_CTE AS ( SELECT @SDATE AS DATE UNION ALL SELECT DATE+1 FROM DATE_CTE WHERE DATE<@EDATE ), --因為資料有百萬筆，所以先將需要的日期區間資料取出 A_CTE AS ( SELECT * FROM @Temp WHERE --PreviousTime和時間區間交集 PreviousTime>=@SDATE AND PreviousTime<=@EDATE+1 OR --TranstionTime和時間區間交集 TranstionTime>=@SDATE AND TranstionTime<=@EDATE+1 OR --PreviousTime和TranstionTime包含整個時間區間 PreviousTime<=@SDATE AND TranstionTime>=@EDATE+1 ), --處理最後一筆資料, --利用ROW_NUMBER函數按照SID排序後，找到每台設備的最後一筆資料， --如果最後一筆資料的結束時間點在最後一天之前，補上最後一段時間區間後加入原資料 B_CTE AS ( SELECT * FROM A_CTE UNION ALL SELECT NULL AS SID, Equipment AS Equipment, TranstionTime AS PreviousTime, DATEADD(DAY, 0, DATEDIFF(DAY, 0, TranstionTime))+1+'08:00' AS TranstionTime, Status AS PreviousStatus, NULL AS Status FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Equipment ORDER BY SID DESC) AS RID FROM A_CTE ) AS A WHERE RID=1 AND TranstionTime<=@EDATE+1 ), --因為資料可能跨日，所以利用JOIN將跨日資料拆開， --再用DATEDIFF函數算出時間以分鐘為單位 C_CTE AS ( SELECT DT.DATE, Equipment, PreviousStatus AS Status, DATEDIFF(MINUTE, CASE WHEN PreviousTime > DT.DATE THEN PreviousTime ELSE DT.DATE END, CASE WHEN TranstionTime < DT.DATE+1 THEN TranstionTime ELSE DT.DATE+1 END ) AS Time FROM B_CTE LEFT JOIN DATE_CTE AS DT ON PreviousTime>=DT.DATE AND PreviousTime<=DT.DATE+1 OR TranstionTime>=DT.DATE AND TranstionTime<=DT.DATE+1 OR PreviousTime<=DT.DATE AND TranstionTime>=DT.DATE+1 ), --使用PIVOT將狀態欄位直轉橫，就是期望的結果 D_CTE AS ( SELECT CONVERT(NVARCHAR(20), PV.DATE, 111) AS 日期, PV.Equipment AS 設備, PV.停機 AS 停機時間, PV.運行 AS 運行時間, PV.維修 AS 維修時間, CASE WHEN 停機 IS NULL THEN 0 ELSE 停機 END + CASE WHEN 運行 IS NULL THEN 0 ELSE 運行 END + CASE WHEN 維修 IS NULL THEN 0 ELSE 維修 END AS 總共時間 FROM ( SELECT * FROM C_CTE ) AS T PIVOT ( SUM(Time) FOR Status IN ([停機], [運行], [維修]) ) AS PV ) SELECT * FROM D_CTE ORDER BY 日期, 設備