I'm selecting year, month and net sales from invoice table. The problem is that if there's no data under specific month, there will be no rows for that month. Can you help me? Net sales should be zero if there is not any data.
DATEPART(year, date) as 'year',
DATEPART(month, date) as 'month',
SUM(netsales) as netsales
date >= '2015-01-01'
AND date <= '2016-12-31'
You need a
calendar table and
;with calendar as ( select cast('2015-01-01' as date) as dates -- start date union all select dateadd(mm,1,dates) from cte where dates < '2016-12-31' -- end date ) SELECT DATEPART(year, c.dates) as 'year', DATEPART(month, c.dates) as 'month', SUM(netsales) as netsales FROM calendar C left join invoice i on c.dates = cast(i.[date] as date) GROUP BY DATEPART(year, date), DATEPART(month, date)
I have generates dates on the fly using
Recursive CTE, but I will always suggest to create a calendar table physically and use it in such queries