此处采用窗口功能 优先于(>,datediff(和PIVOT。

<>说明: 没有跨过天数的逻辑(即午夜)。 如果需要,则不清楚。

<><>Example>

Select *
 From  (
        Select Date  = convert(date,logtime)
              ,MachineName
              ,Status
              ,Hours = datediff(hour,logtime,lead(logtime,1) over (partition by MachineName order by LogTime))
         From YourTable
       ) src
 Pivot ( sum(Hours) for Status in ([Good],[Bad]) ) pvt
 

Results

Date        MachineName Good    Bad
2024-07-03  Mac1        20      2

以预期结果为例,你没有显示<条码>。 例如,你按日期分组。 举例来说,按日期进行分组,并计算整个午夜的幅度。

段 次 页 次

( 2024-03-07 23:10:00 )->( 2024-03-07 23:59:59 ) date 2024-03-07
( 2024-03-08 00:00:00 )->( 2024-03-08 03:10:00 ) date 2024-03-08

See example

select Machine,logDate
  ,sum(case when Status= good  then elong end) GoodLong
  ,sum(case when Status= bad  then elong end) BadLong
  ,round(cast(sum(case when Status= good  then elong end) as float)/3600.0,4) GoodLongH
  ,round(cast(sum(case when Status= bad  then elong end) as float)/3600.0,4) BadLongH
from(
  select *,cast(logtime as date) LogDate
    ,datediff(s,logtime,lead(logtime,1,logtime)over(order by logtime)) elong
 from( -- for over-midnight periods
   select Machine,  Status,n
    ,case  when n=1 and logDate<>nextDate then 
       cast(nextDate as datetime)
     else logTime
     end LogTime 
    ,case when n=0 and logDate<>nextDate then dateadd(s,-1,cast(nextDate as datetime))
     else nextTime
     end nextTime
   from(
     select *
       ,cast(logtime as date) logDate
       ,lead(logtime,1,logtime)over(order by logtime) nextTime
       ,cast(lead(logtime,1,logtime)over(order by logtime) as date) nextDate
     from Logwork) a
     left join (select 0 n union all select 1 )t(n)
        on (n=0  ) or (n=1 and logDate<>nextDate )
   )b
 )с
group by Machine,LogDate

每天24小时

insert into LogWork values
 ( Mac1 , good , 2024-03-07 1:10:05 )
,( Mac1 , good , 2024-03-07 2:15:00 ) 
,( Mac1 , good , 2024-03-07 3:20:00 ) 
,( Mac1 , bad , 2024-03-07 4:10:00 ) 
,( Mac1 , good , 2024-03-07 5:10:00 ) 
,( Mac1 , good , 2024-03-07 11:10:00 ) 
,( Mac1 , bad , 2024-03-07 15:10:00 ) 
,( Mac1 , good , 2024-03-07 16:10:00 ) 
,( Mac1 , good , 2024-03-07 23:10:00 )
,( Mac1 , bad , 2024-03-08 03:10:00 )
,( Mac1 , good , 2024-03-08 05:10:00 )
;

产出

计算th夜产出的顺序