首先，使用日期表枚举没有周末的日期（如果需要，您也可以休假）。然后，通过使用非等额合并，将周期扩展为每行一天。
然后，您可以使用技巧来确定连续的日期。这个技巧是为每个id生成一个序号，然后从日期的序号中减去它。这是连续几天的常数。最后一步只是一个汇总。
结果查询如下所示：
with d as (
select d.*, row_number() over (order by date) as seqnum
from dates d
where day not in ('Saturday', 'Sunday')
)
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
(d.seqnum - row_number() over (partition by id order by ds.date) ) as grp
from table t join
d ds
on ds.date between t.startdate and t.enddate
) t
group by t.id, grp;
编辑：
with d as (
select d.*, row_number() over (order by date) as seqnum
from datetable d
where day not in ('Saturday', 'Sunday')
)
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
(ds.seqnum - row_number() over (partition by id order by ds.date) ) as grp
from (select t.*, 'abc' as id from table1 t) t join
d ds
on ds.dateid between t.startdate and t.enddate
) t
group by grp;
我相信这是可行的，但日期表中并未包含所有日期。
602392714
清零编程群