问TSQL -计算两次击打之间的时间，但不包括中断时间

EN

WITH time_off as (
    SELECT * ,
           CASE WHEN w.in_w < b.out_b AND w.out_w > b.in_b
                THEN 'overlap' 
           END as overlap,
           CASE WHEN w.in_w < b.in_b 
                THEN b.in_b
                ELSE w.in_w
           END as break_start,
           CASE WHEN w.out_w > b.out_b 
                THEN b.out_b
                ELSE w.out_w
           END as break_end       
    FROM workers w
    CROSS JOIN breaks b
), break_total as (
    SELECT worker_id, in_w, out_w, SUM (CASE WHEN overlap = 'overlap'
                                             THEN datediff(minute, break_start,break_end)
                                             ELSE 0
                                        END) as break_total
    FROM time_off
    GROUP BY worker_id, in_w, out_w
)
SELECT worker_id,
       datediff(minute, in_w, out_w) - break_total as total_minutes
FROM  break_total 

SELECT * FROM time_off;
SELECT * FROM break_total;