按DateKey统计班次总工时，含午餐时段扣30分钟的SQL实现方法

基础表信息 #

你使用的班次表名为Shifts，样例数据如下：

ID Key  DateKey  Start                End                  Hours     Segment 
1  1001 20210101 2021-01-01 09:00:00  2021-01-01 09:00:00  4.000000  On-Call
2  1001 20210101 2021-01-01 11:00:00  2021-01-01 11:15:00  0.250000  Break
3  1001 20210102 2021-01-02 13:00:00  2021-01-01 19:00:00  6.000000  On-Call
4  1001 20210102 2021-01-02 15:00:00  2021-01-01 15:15:00  0.250000  Break
5  1001 20210102 2021-01-02 17:00:00  2021-01-01 17:30:00  0.500000  Lunch
6  1001 20210103 2021-01-03 09:00:00  2021-01-03 16:00:00  7.000000  On-Call
7  1001 20210103 2021-01-03 11:00:00  2021-01-03 11:15:00  0.250000  Break
8  1001 20210103 2021-01-03 13:00:00  2021-01-03 13:30:00  0.500000  Lunch
9  1002 20210104 2021-01-04 09:00:00  2021-01-04 09:00:00  4.000000  On-Call
10 1002 20210104 2021-01-04 11:00:00  2021-01-04 11:15:00  0.250000  Break
11 1002 20210105 2021-01-05 07:00:00  2021-01-05 14:00:00  7.000000  On-Call
12 1002 20210105 2021-01-05 09:00:00  2021-01-05 09:15:00  0.250000  Break
13 1002 20210105 2021-01-05 11:00:00  2021-01-05 11:30:00  0.500000  Lunch

统计规则 #

• 按DateKey维度汇总对应日期的原始总工时，例如DateKey=20210101对应的ID为1、2的记录，汇总原始总工时为4.250000
• 原始总工时计算完成后，若该DateKey下存在Segment取值为Lunch的记录，则从总工时中固定扣除0.5小时（30分钟），得到最终有效工时。例如DateKey=20210102对应的ID为3、4、5的记录原始汇总总工时为6.750000，扣除0.5小时后得到最终有效工时

实现语句 #

直接使用分组聚合+条件判断即可实现，代码如下：

SELECT
    DateKey,
    SUM(Hours) AS 原始总工时,
    SUM(Hours) - CASE
        WHEN MAX(CASE WHEN Segment = 'Lunch' THEN 1 ELSE 0 END) = 1 THEN 0.5
        ELSE 0
    END AS 最终有效工时
FROM Shifts
GROUP BY DateKey;

如果你需要按「员工+日期」维度统计（即区分同一天不同员工的工时），只需要把Key字段加入SELECT和GROUP BY子句即可。

样例数据运行结果 #

内容的提问来源于stack exchange，提问作者SQL Grab