I am trying to write a query for a sales type report and want to get a record for each month of the year for each sales person regardless if they have any sales or if the month has occurred. This query gives me a record for each month, but when I try to add in my table with the sales data, I only get a record if the sales person has sales. I would like to have 12 rows for each sales person.
WITH daterange AS (SELECT CAST(DATEADD(yy,DATEDIFF(yy,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)),0) AS datetime) AS DateValue
SELECT DateValue + 1
FROM daterange AS daterange_2
WHERE (DateValue + 1 <= DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,-1,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)),0))))
,daterange2 AS (SELECT * FROM daterange WHERE DAY(DateValue) = 1)
SELECT DateValue from daterange2
OPTION (MAXRECURSION 0)