TSQL - Calculate time between punches but exclude

问题:

Not sure if this is a unique question or not.

I'm needing to get calculated punch in and punch out times for a labor tracking system. Our crew has breaks from 10-10:20 AM and from 1-1:20 PM.

What I need to figure out is how to subtract this time from a total if they are still punched in during these breaks.

For example, if Joe punches in to a job at 09:53 and punches out at 10:23, I want it to show 10 minutes instead of 30.

How could I do this for few "blackout" times of 10-10:20, 1-1:20, 11-11:20, and 5-5:20?

回答1:

This return the total of work minutes. This check if the worker time overlap with each break time and then change it to indicate how much overlap was.

Then calculate the total of minutes in break and finally subtract for the total of time worker punch in_out.

SQL DEMO

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 

For some debug do:

SELECT * FROM time_off;
SELECT * FROM break_total;