I have a simple date dimension,
SELECT [actualDate]
FROM (
VALUES ('2021-09-20')
, ('2021-09-21')
, ('2021-09-22')
, ('2021-09-23')
, ('2021-09-24')
, ('2021-09-25')
, ('2021-09-26')
, ('2021-09-27')
, ('2021-09-28')
) as t(actualDate)
I'm attempting to write a Window function (without much luck..) to net the following result.
where 1 is today and 0 is yesterday.
---------- ----------
|actualDate|dateOffset|
---------- ----------
|2021-09-20|-4 |
|2021-09-21|-3 |
|2021-09-22|-2 |
|2021-09-23|-1 |
|2021-09-24|0 |
|2021-09-25|1 |
|2021-09-26|2 |
|2021-09-27|3 |
|2021-09-28|4 |
---------- ----------
Is anyone able to help or guide me in the direction of a dupe as I'm unsure what to search for.
CodePudding user response:
Assuming the dates are continuous, we don't even need analytic functions but instead we can just use DATEDIFF
:
SELECT
actualDate,
1 DATEDIFF(day, CAST(GETDATE() AS date), actualDate) dateOffset
FROM yourTable
ORDER BY actualDate;