Rank Date based on today’s date

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         |
+----------+----------+

What can I try next? I’m unsure what to search for.

Answer

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;

Demo