SQL Server 2017

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

--【建立測試資料】
--Drop table #Table1
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'運行')
;
--select * from #Table1

--【Script】
declare  @DateToday datetime  = CONVERT(datetime ,CONVERT (date, '2018/06/10')) --資料以日期6/10來測試
	,@DayRange int = 7 --決定可以查幾天資料
	,@TimeDiff int = 8 --決定以幾點劃分一天
;
--【第一步】找出所有的機台
with CTE_Equips as (
	select [Equipment]
	from #Table1
	group by [Equipment]
--【第二步】使用CTE遞迴得出一個禮拜資料,並以機台為一方資料left join日期
),CTE_DATES as (
  SELECT 1 as i
  UNION ALL
  SELECT  i + 1
  FROM CTE_DATES 
  WHERE i < @DayRange
)
,CTE_BASIC_DATA as (
	select * from CTE_Equips
	left join (
		select *
			,DATEADD ( hour,@TimeDiff,[date]) [StartDatetime]
			,DATEADD ( hour,@TimeDiff+24,[date]) [EndDatetime]
		from (
			select DATEADD ( day,-i,@DateToday) [date] 
			from CTE_DATES	
		) T
	) T on 1 =1
)
--【第三步】按照機台以時間排序，排出從舊到新的SID，方便之後以SID+1-1方式取得上一個操作或下一個操作的資料
,CTE_RANK as (
		select *,row_number() over (partition by [Equipment] order by [PreviousTime]) rnk from #Table1 
)
,CTE_Result as (
	select  T1.* 
		,case when T3.[SID] is not null then T3.[SID]
		else T2.[SID]
		end  [SID]
		,case when T3.[SID] is not null then T1.[StartDatetime]
		else T2.[PreviousTime]
		end  [PreviousTime]
		,case when T3.[SID] is not null or T2.[TranstionTime] > T1.[EndDatetime] then T1.[EndDatetime]
			else T2.[TranstionTime]
		end  [TranstionTime]
		/*
			假如是該機台最後一筆資料,代表之後動作都是一樣
			舉例:
				前一天晚上7點最後一個動作是運行
				那麼7點到早上八點狀態都會是運行
				所以這邊要抓現在狀態 T3.[Status]

反之不是最後一個動作
				那麼要抓前一個狀態 T3.[PreviousStatus]
		*/
		,row_number() over (partition by T1.[Equipment] order by T2.[PreviousTime] desc) descrnk
		,case when T3.[SID] is not null then 
			case when row_number() over (partition by T1.[Equipment] order by T2.TranstionTime desc) =1 then T3.[Status]
			else T3.[PreviousStatus]
			end 
		else T2.[PreviousStatus]
		end  [PreviousStatus]
		--假如是跨日資料不會有T3的資料,所以狀態要抓T2為準
		,case when T3.[SID] is not null then null
		else T2.[Status]
		end  [Status]
		/*
			假如當天第一筆資料時間不是當天早上8點
			這邊先把排名SID紀錄下來 (rnk - 1) = 該機台上一個動作資料
			接著後面去抓前一個歷史資料的執行時間
		*/
		,case when row_number() over (partition by T1.[Equipment],T1.[Date] order by T2.[PreviousTime]) = 1 
			and T2.[PreviousTime] > [StartDatetime]
		then
			T2.rnk -1
		end
		PreviousOne
		--確定是不是該機台最後一筆資料
		,case when row_number() over (partition by T1.[Equipment] order by T2.[PreviousTime] desc) =1 
			and T2.TranstionTime < [EndDatetime]
		then
			'Y'
		end as MaxOne
	from CTE_BASIC_DATA T1
	left join CTE_RANK T2 on T1.[Equipment] = T2.[Equipment] 
		and T2.[PreviousTime] >=  [StartDatetime] and T2.[PreviousTime] < [EndDatetime]	
	left join CTE_RANK T3 on T2.sid is null
		and T1.[Equipment] = T3.[Equipment] 
		and T3.sid = (select max(sid) from #Table1 T4 where T4.[Equipment] = T1.[Equipment] and T4.[PreviousTime] < T1.[StartDatetime])
	where T3.[SID] is not null or T2.[SID] is not null
) 
--SELECT * FROM CTE_Result order by Equipment,[Date]
,CTE_GROUP as (
	select * from (
		--前一天資料跨日資料組合
		select T1.Equipment,T1.[date],T1.StartDatetime,T1.EndDatetime 
			,T1.[SID]
			,T1.StartDatetime  [PreviousTime]
			,T1.[PreviousTime] TranstionTime
			,T2.PreviousStatus [Status]
		from (
			select * from CTE_Result
			where 1=1
			and PreviousOne is not null
			and PreviousOne > 0 --排除第一筆資料之前的資料
		) T1
		left join CTE_RANK T2 on T1.Equipment = T2.Equipment and T1.PreviousOne = T2.rnk

union all
	
		--把前面處理的資料中 - 最後一筆資料組合
		select T1.Equipment,T1.[date],T1.StartDatetime,T1.EndDatetime 
			,T1.[SID]
			,T1.TranstionTime  [PreviousTime]
			,T1.EndDatetime TranstionTime
			,T1.[Status] 
		from (
			select * from CTE_Result
			where MaxOne = 'Y'
		) T1

union all

select T1.Equipment,T1.[date],T1.StartDatetime,T1.EndDatetime 
			,T1.[SID] ,T1.PreviousTime,T1.TranstionTime,T1.PreviousStatus [Status]
		from CTE_Result T1
	) T
)
--SELECT * FROM CTE_GROUP
select *,ISNULL(停機時間,0)+ISNULL(運行時間,0)+ISNULL(維修時間,0) 總共時間 from (
	select Convert(date,[Date]) 日期,Equipment 設備
		,sum(case when [Status] = N'停機' then  datediff(minute,PreviousTime,TranstionTime) end)   停機時間 
		,sum(case when [Status] = N'運行' then  datediff(minute,PreviousTime,TranstionTime) end)   運行時間 
		,sum(case when [Status] = N'維修' then  datediff(minute,PreviousTime,TranstionTime) end)   維修時間 
	from CTE_GROUP
	group by Equipment,[Date]
) T
order by 日期,設備

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