Join Generated Date Sequence

Currently I’m trying to join a date table to a ledger table so I can fill the gaps of the ledger table whenever there are no transactions in certain instances (e.g. there are transactions on March 1st and in March 3rd, but no transaction in March 2nd. And by joining both tables March 2nd would appear in the ledger table but with 0 for the variable we’re analyzing.)

The challenge is that I can’t create a Date object/table/dimension because I don’t have permissions to create tables in the database. Therefore I’ve been generating a date sequence with this code:

DECLARE @startDate date = CAST('2016-01-01' AS date),
        @endDate date = CAST(GETDATE() AS date);

SELECT DATEADD(day, number - 1, @startDate) AS [Date]
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY n.object_id
        )
    FROM sys.all_objects n
    ) S(number)
WHERE number <= DATEDIFF(day, @startDate, @endDate) + 1;

So, is there the possibility to join both tables into the same statement? Let’s say the ledger table looks like this:

SELECT
 date,cost
FROM ledger

I’d assume it can be done by using a subquery but I don’t know how.

Thank you.

Answer

There is a very good article by Aaron Bertrand showing several methods for generating a sequence of numbers (or dates) in SQL Server: Generate a set or sequence without loops – part 1.

Try them out and see for yourself which is faster or more convenient to you. (spoiler – Recursive CTE is rather slow)

Once you’ve picked your preferred method you can wrap it in a CTE (common-table expression).

Here I’ll use your method from the question

WITH
CTE_Dates
AS
(
    SELECT 
        DATEADD(day, number - 1, @startDate) AS dt
    FROM (
        SELECT ROW_NUMBER() OVER (
            ORDER BY n.object_id
            )
        FROM sys.all_objects n
        ) S(number)
    WHERE number <= DATEDIFF(day, @startDate, @endDate) + 1
)
SELECT
   ...
FROM
    CTE_Dates
    LEFT JOIN Ledger ON Ledger.dt = CTE_Dates.dt
;