SQL统计查询问题：按Subname统计Returned与Enforcement_date日期对比

原始数据表 #

期望统计结果 #

需求说明 #

• 按Project、Subname分组，每组取非空的Enforcement_date作为ED；
• 统计每组总条数Count；
• 统计Returned ≤ Exp_return_date的数量；
• 统计Returned < Enforcement_date的数量（需处理Enforcement_date为空的情况）；
• 统计Returned < Enforcement_date+1个月的数量；
• 统计Returned为空的数量。

当前问题SQL #

SELECT [Project], [Subname], MIN(Enforcement_date) as ‘ED’,
COUNT(*) as 'Count', 
COUNT(case when Returned <= Exp_return_date then 1 else null end) as ‘<= Exp_return_date’,

COUNT(case when Returned <= Enforcement_date then 1 else null end) as ‘< Enformcement_date’, -- didn’t works, Enforcement_date is not always entered.
COUNT(case when Returned <= Enforcement_date+1month then 1 else null end) as ‘< Enformcement_date’, -- how can I calculated this?

COUNT(case when Returned <> '' then 1 else null end) as ‘None’

FROM *******
GROUP BY [Project], [Subname]

问题点：

1. 未处理Enforcement_date为空的场景，导致对应统计项错误；
2. 未正确实现Enforcement_date加1个月的日期计算与对比。

修正后的SQL（SQL Server版本） #

SELECT 
    Projectname AS Project,
    Subname,
    MAX(Enforcement_date) AS ED,
    COUNT(*) AS Count,
    -- 统计Returned ≤ Exp_return_date的数量
    COUNT(CASE WHEN TRY_CAST(Returned AS DATE) <= TRY_CAST(Exp_return_date AS DATE) THEN 1 END) AS '<=exp_return_date',
    -- 统计Returned < Enforcement_date，空ED时返回NULL
    CASE WHEN MAX(Enforcement_date) IS NOT NULL THEN
        COUNT(CASE WHEN TRY_CAST(Returned AS DATE) < TRY_CAST(MAX(Enforcement_date) OVER(PARTITION BY Projectname, Subname) AS DATE) THEN 1 END)
    ELSE NULL END AS '<enforcement_date',
    -- 统计Returned < Enforcement_date+1个月，空ED时返回NULL
    CASE WHEN MAX(Enforcement_date) IS NOT NULL THEN
        COUNT(CASE WHEN TRY_CAST(Returned AS DATE) < DATEADD(MONTH, 1, TRY_CAST(MAX(Enforcement_date) OVER(PARTITION BY Projectname, Subname) AS DATE)) THEN 1 END)
    ELSE NULL END AS 'eforncement_date_+1_month',
    -- 统计Returned为空的数量
    COUNT(CASE WHEN Returned IS NULL OR Returned = '' THEN 1 END) AS 'None'
FROM YourTableName -- 替换为实际表名
GROUP BY Projectname, Subname

关键修正说明 #

1. ED字段获取：用MAX(Enforcement_date)确保取到分组内非空的执行日期（同一组非空值一致，MAX/MIN均可）；
2. 空值处理：通过CASE WHEN MAX(Enforcement_date) IS NOT NULL判断，空ED时对应统计项返回NULL，匹配期望结果；
3. 日期计算：用DATEADD(MONTH, 1, ...)实现加1个月的逻辑，不同数据库需调整函数：
   • MySQL：DATE_ADD(MAX(Enforcement_date), INTERVAL 1 MONTH)
   • Oracle：ADD_MONTHS(MAX(Enforcement_date), 1)
4. 日期转换：用TRY_CAST（SQL Server）或对应数据库的日期转换函数，避免字符串格式错误导致查询失败；
5. None统计修正：原逻辑错误，改为统计Returned为空或空字符串的记录数。

内容的提问来源于stack exchange，提问作者Pas Cal