CODEWITHSUNDEEP
sqlsql-servermissing-data
noob5eva
noob5eva

Reputation: 31

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.

Upvotes: 0

Views: 237

Answers (1)

izzy
izzy

Reputation: 807

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.

Upvotes: 1

Related Questions