Skip to content
Advertisement

finding missing dates in a time interval (SQL)

I am a bit of a noob with SQL, so I was searching for some bit of code that might help me find missing date values for a time interval when I stumbled upon this code.

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME

SET @StartDate ='2014-03-01' SET @EndDate = GETDATE()

;WITH Dates(Date) AS
(
    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate)) AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date) AS Date
    FROM Dates
    WHERE Date <= @EndDate
)
SELECT d.Date, r.Value
FROM Dates d
LEFT JOIN Times r ON d.Date = r.Date

Link to the code

It works really well for my problem but I am not able to understand how it increments the date.

I would ask the author but their blog no longer exists and their twitter is inactive too.

Edit: someone said the post is lacking a question. I want to know how this CTE is recursively adding the +1 to each date from @StartDate to @EndDate.

Advertisement

Answer

This is a recursive CTE, or Common Table Expression.

The first line of of the CTE SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate)) AS Date is the seed, or root portion. The next UNION section takes that date, adds a day, and recurses.

Notice that inside the Dates block you are selecting FROM Dates, so it will continue produce rows with an incremented date until the WHERE clause is satisfied.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement