如何按截止日期计算一周内任务状态？SQL查询结果异常排查

问题描述 #

我希望根据截止日期计算任务状态，但当前SQL查询中ID为A的Completed列显示0，而非预期的1。

任务数据表 #

当前SQL代码 #

DECLARE @currentWeek int
SELECT @currentWeek = DATEPART(week, GETDATE())
SELECT  
    Employee as ID, 
    Sum(CASE WHEN Status  = 'Completed'  THEN  1 ELSE 0 END) as Completed,
    (Sum(CASE WHEN Status  = 'In progress'  THEN 1 ELSE 0 END) +    
    Sum(CASE WHEN Status  = 'Not started'  THEN 1 ELSE 0 END)) as Remaining,
    Sum(CASE WHEN EndDate  >  convert(varchar,GETDATE(),103) THEN 1 ELSE 0 END) as OverDue  
FROM Task
WHERE DATEPART(week, [EndDate]) = @currentWeek  and Status !=  'Completed' 
Group By Employee

预期输出（当日日期为2023-06-12时） #

问题根源 #

1. WHERE子句过滤了已完成任务：原查询中Status != 'Completed'直接排除所有已完成的任务，导致统计Completed数量时完全取不到ID为A的那条已完成记录，结果必然为0。
2. 逾期判断逻辑错误：将GETDATE()转为字符串后与日期类型的EndDate比较，会触发隐式类型转换，导致比较逻辑偏离预期。正确的逾期判断应该是截止日期早于当前日期。
3. 列名笔误：数据表中不存在Employee列，原查询里的Employee as ID属于错误引用，会引发列不存在的报错。

修正后的SQL代码 #

DECLARE @currentWeek int
SELECT @currentWeek = DATEPART(week, GETDATE())
SELECT  
    ID, 
    SUM(CASE WHEN Status = 'Completed' THEN 1 ELSE 0 END) as Completed,
    SUM(CASE WHEN Status IN ('In progress', 'Not started') THEN 1 ELSE 0 END) as Remaining,
    SUM(CASE WHEN EndDate < CAST(GETDATE() AS DATE) THEN 1 ELSE 0 END) as OverDue  
FROM Task
WHERE DATEPART(week, [EndDate]) = @currentWeek
GROUP BY ID

修正说明 #

• 移除WHERE子句中的Status != 'Completed'，确保所有状态的任务都被纳入统计范围。
• 简化Remaining的计算逻辑，用IN条件合并两个CASE语句，代码更简洁。
• 修正逾期判断：将GETDATE()转换为纯日期类型后与EndDate比较，当截止日期早于当前日期时标记为逾期，符合业务预期。
• 修正列名错误，将Employee改为数据表实际存在的ID列。

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