SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 日期, 設備

日期	設備	停機時間	運行時間	維修時間	總共時間
2018/06/06	A	1200	null	null	1200
2018/06/07	A	180	180	1080	1440
2018/06/07	B	120	180	1080	1380
2018/06/08	A	null	null	1440	1440
2018/06/08	B	null	null	1440	1440
2018/06/09	A	120	1200	120	1440
2018/06/09	B	120	1200	120	1440