TSQL -> Recursive retrieval of data using CTE

Common Table Expressions can be used for writing recursive queries.


/* In the below example we define recursive CTE statement "a" that will return date entries sequence in the period between 1999-Feb-12 And 1999-March-15. The underlying select joins Sales totals from Invoices table with the date sequence returned by CTE */

WITH a(d)
AS
(
   SELECT (CONVERT(datetime, '19990212')) AS d
   UNION ALL
   SELECT d + 1 FROM a WHERE d < '19990315'
)
SELECT a.d AS the_date, SUM(isnull(b.DocTotal,0)) AS day_total
FROM a LEFT JOIN dbo.Invoice AS b ON a.d = b.DocDate
GROUP BY a.d
ORDER BY a.d
OPTION (Maxrecursion 500)

/* Maxrecursion Option is used for the safety purposes */



Query results shows all days in the period, but only those that had sales record came with day_total different from 0 :

the_dateday_total
1999-02-120
1999-02-130
1999-02-1410200
1999-02-150
1999-02-160
1999-02-170
1999-02-180
1999-02-19375
1999-02-200
......


sqlexamples.info