如何使用DB2 SQL筛选整月工作且全月code为45的人员？

需求说明 #

从给定DB2数据表中筛选满足以下条件的记录：

• 人员在某个完整自然月内全程工作（该月每一天都被其Date1至Date2的区间覆盖）
• 该人员在对应月份的所有关联记录中，code字段均为45

解决方案SQL #

WITH monthly_coverage AS (
    -- 拆分每条记录涉及的所有月份，统计覆盖情况与code合规性
    SELECT 
        ID,
        NAME,
        YEAR(m.month_start) AS year,
        MONTH(m.month_start) AS month,
        -- 标记该月是否存在非45的code
        MAX(CASE WHEN code != 45 THEN 1 ELSE 0 END) AS has_non_45,
        -- 计算该人员该月实际覆盖的天数
        SUM(
            DAYS(LEAST(Date2, LAST_DAY(m.month_start))) 
            - DAYS(GREATEST(Date1, m.month_start)) 
            + 1
        ) AS covered_days,
        -- 该自然月的总天数
        DAYS(LAST_DAY(m.month_start)) - DAYS(m.month_start) + 1 AS total_days
    FROM 
        your_table t
    -- 生成记录时间区间内的所有自然月（处理Date2为null的情况，默认视为当前日期，可根据业务调整为远未来日期）
    LEFT JOIN TABLE(
        MONTHS_BETWEEN(
            COALESCE(Date2, CURRENT_DATE), 
            Date1
        ) + 1 TIMES GENERATE_DATE(
            DATE(YEAR(Date1) || '-' || MONTH(Date1) || '-01'), 
            1, 
            'MONTH'
        )
    ) m(month_start)
        ON m.month_start <= COALESCE(Date2, CURRENT_DATE)
        AND LAST_DAY(m.month_start) >= Date1
    GROUP BY 
        ID, NAME, YEAR(m.month_start), MONTH(m.month_start)
),
valid_months AS (
    -- 筛选出全月覆盖且code全为45的人员-月份组合
    SELECT 
        ID, NAME, year, month
    FROM 
        monthly_coverage
    WHERE 
        has_non_45 = 0
        AND covered_days = total_days
)
-- 关联原表，取出符合条件的记录
SELECT 
    t.ID, t.NAME, t.Date1, t.Date2, t.code
FROM 
    your_table t
JOIN valid_months vm
    ON t.ID = vm.ID
    AND t.NAME = vm.NAME
    -- 确保原记录与有效月份存在时间重叠
    AND (
        t.Date1 <= LAST_DAY(DATE(vm.year || '-' || vm.month || '-01'))
        AND COALESCE(t.Date2, CURRENT_DATE) >= DATE(vm.year || '-' || vm.month || '-01')
    )
ORDER BY 
    t.ID, t.Date1;

逻辑说明 #

1. monthly_coverage CTE：将每条记录的时间区间拆分为涉及的自然月，计算每个月的覆盖天数，同时检查该月是否存在非45的code。
2. valid_months CTE：从统计结果中筛选出完全覆盖整月且code全为45的人员-月份组合。
3. 最终关联原表，取出所有与有效月份有时间重叠的记录，即为目标结果。

注意：请将SQL中的your_table替换为实际数据表名；若Date2为null表示永久有效，可将COALESCE(Date2, CURRENT_DATE)改为COALESCE(Date2, DATE('9999-12-31'))。

内容的提问来源于stack exchange，提问作者Tick _Tack