如何对比最后一次运行日期与当前日期并计算间隔天数？

SELECT
    CAST(GETDATE() AS Date) AS Today,
    Run_Date,
    COUNT(Run_Date) AS Daily_Count,
    Days_Since_Last_Run_Date = CASE 
        WHEN ROW_NUMBER() OVER(ORDER BY Run_Date DESC) = 1 
        THEN CAST(DATEDIFF(DAY, Run_Date, CAST(GETDATE() AS Date)) AS NVARCHAR(10))
        ELSE ''
    END
FROM tblUnitsOfWork
WHERE Run_Date >= '2023-07-10'
GROUP BY Run_Date
ORDER BY Run_Date DESC;

关键调整说明 #

• 移除冗余的DISTINCT：GROUP BY Run_Date已经确保每个运行日期仅返回一行，无需额外去重
• 重构间隔天数计算逻辑：
  • 通过ROW_NUMBER() OVER(ORDER BY Run_Date DESC)定位最后一次运行的记录（行号=1）
  • 仅对该行用DATEDIFF(DAY, Run_Date, CAST(GETDATE() AS Date))计算与当前日期的间隔天数，其他行留空
• 统一日期格式：用CAST(GETDATE() AS Date)去除时间部分，避免时间干扰日期对比的准确性

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