SQL Server 2017

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

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	停機	運行

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	null	null	120
2018-06-05	D	120	null	null	120
2018-06-06	A	1200	null	null	1200
2018-06-06	C	1440	null	null	1440
2018-06-06	D	1440	null	null	1440
2018-06-07	A	180	180	1080	1440
2018-06-07	B	120	180	1080	1380
2018-06-07	C	60	1380	null	1440
2018-06-07	D	null	1440	null	1440
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

Warning: Null value is eliminated by an aggregate or other SET operation.