How to get date difference from a column?

Question

The calculation for the difference between each date is working fine except for the minus sign daydiff column. My requirement is to remove the minus in daydiff column. Kindly assist me.

The table below is how it's being displayed in my SQL server

Date	Weight	Weightdiff	Daydiff
Jan 14, 2025	18	3	-5
Jan 09, 2025	15	5	-20
Dec 20, 2024	10	0	0

This is the code that l had tried

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

Expected output

Date	Weight	Weightdiff	Daydiff
Jan 14, 2025	18	3	5
Jan 09, 2025	15	5	20
Dec 20, 2024	10	0	0

Answer 1

datediff "subtracts" the second argument (the "startdate") from the third argument (the "enddate"). If you're getting the opposite sign to what you expect, you can flip the two arguments:

DATEDIFF(DAY, LAG(Date) OVER (ORDER BY Date), Date) AS Daydiff