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