SQL Server中如何去除日期差列（Daydiff）的负号？

问题背景 #

日期差值计算逻辑正常，但Daydiff列显示负号，需要将其转为非负数。当前查询输出如下：

当前执行的SQL代码：

SELECT  
    format(Date, 'MMM,dd, yyy') as Date,
    Sum(Weight) as Weight,
    Sum(Weight) - LAG (Sum(Weight)) OVER (ORDER BY format(Date, 'MMM,dd, yyy') ) AS Weightdiff,
    DATEDIFF(DAY, Date, LAG(Date) OVER (ORDER BY Date)) AS Daydiff
FROM Store
Group By format(Date, 'MMM,dd, yyy'), Date
Order By format(Date, 'MMM,dd, yyy') DESC

期望得到的结果：

问题原因 #

负号产生的核心是DATEDIFF的参数顺序：DATEDIFF(DAY, Date, LAG(Date) OVER (ORDER BY Date))中，LAG(Date)返回的是上一行更早的日期，函数计算的是「当前日期到更早日期的天数差」，结果自然为负。

两种解决方案 #

方案1：用ABS()取绝对值 #

直接对DATEDIFF的结果取绝对值，强制转为非负数：

SELECT  
    format(Date, 'MMM,dd, yyy') as Date,
    Sum(Weight) as Weight,
    Sum(Weight) - LAG (Sum(Weight)) OVER (ORDER BY format(Date, 'MMM,dd, yyy') ) AS Weightdiff,
    ABS(DATEDIFF(DAY, Date, LAG(Date) OVER (ORDER BY Date))) AS Daydiff
FROM Store
Group By format(Date, 'MMM,dd, yyy'), Date
Order By format(Date, 'MMM,dd, yyy') DESC

方案2：调换DATEDIFF的参数顺序 #

把LAG(Date)放在第一个参数位置，计算「更早日期到当前日期的天数差」，结果直接为正：

SELECT  
    format(Date, 'MMM,dd, yyy') as Date,
    Sum(Weight) as Weight,
    Sum(Weight) - LAG (Sum(Weight)) OVER (ORDER BY format(Date, 'MMM,dd, yyy') ) AS Weightdiff,
    DATEDIFF(DAY, LAG(Date) OVER (ORDER BY Date), Date) AS Daydiff
FROM Store
Group By format(Date, 'MMM,dd, yyy'), Date
Order By format(Date, 'MMM,dd, yyy') DESC

推荐方案2，因为它更贴合“计算相邻记录日期间隔”的业务逻辑，避免无意义的绝对值操作。

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