试试下面的那个-这是一个 gap & island 问题

select sum(duration) as total_duration
from
(
select t.*,
       row_number() over (order by visitstart) as seqnum,
       row_number() over (partition by case when paycode='Break' then 1 else 0 end order by visitstart) as seqnumt
from yourtablename t
)A 
where paycode<>'Break'
group by (seqnum-seqnumt)

您可以通过对“break”出现的次数求和来分配组。然后你就可以聚合了。我不太确定最终的持续时间是如何定义的——特别是是否包含“break”行。假设是：

select min(visitstart), max(visitstart),
       sum(duration)
from (select t.*,
             sum(case when paycode = 'BREAK' then 1 else 0 end) over (order by visitstart) as grp
      from t
     ) t
where paycode <> 'BREAK'
group by grp

所以，史蒂夫c，我试着帮你。。。我做了几次cte
cte（ctebreak）-您只需选择中断。
cte（breaks\u chains）-为每个中断编号（因为不能使用行\u numbing）
断链-成对获得断链间隔（例如时间第一次断链-时间第二次断链，时间第二次断链-时间第三次断链）
注意：如果它是最后一个间隔，那么列结束符将替换为getdate（），只是为了关闭间隔
最后，我们将我们的表与表类型（而不是表名）结合起来

--just get rows with BREAK paycode
 with cteBreak as
  (
    select * from Table where paycode='BREAK'
  ),

 --numbering breaks (=row_numbering() in later version sql server)
 breaks_chains as
 (
  select cte1.visitstart visit_st,count(cte2.visitstart)+1 Num from cteBreak cte1
  left join cteBreak cte2
  on cte1.visitstart>cte2.visitstart
  group by cte1.visitstart
 ),

 --get pairs as "start_break - end_break"
 break_interval as
 (
  select t1.visit_st begin_break, COALESCE(t2.visit_st,getdate()) as end_break,t1.Num from 
  breaks_chains t1
  left join breaks_chains t2
  on t2.Num-t1.Num=1
 )

 select sum(duration) from
 (
 select duration,paycode,
  case
     when 
        (Table.visitstart>break_interval.begin_break
         and Table.visitstart<break_interval.end_break)
     then break_interval.end_break    

     when (Table.visitstart<break_interval.begin_break and Num=1)
     then  break_interval.begin_break
 end NumGroup
 from Table
 left join break_interval
 on 
   (Table.visitstart>break_interval.begin_break  
   and Table.visitstart<break_interval.end_break)
 or 
   (Table.visitstart<break_interval.begin_break and Num=1) --for first sequence (before first break)
 )t
 where paycode<>'BREAK'
 group by NumGroup

p、 如果你有问题，请写在评论中）