/* 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 */
|