CODEWITHSUNDEEP
sqlsql-serverdatepart
lingo
lingo

Reputation: 1908

SELECT SQL data based on date, fill month

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.

SELECT 
    DATEPART(year, date) as 'year', 
    DATEPART(month, date) as 'month', 
    SUM(netsales) as netsales 
FROM invoice 
WHERE 
    date >= '2015-01-01' 
    AND date <= '2016-12-31' 
GROUP BY 
    DATEPART(year, date), 
    DATEPART(month, date)

Thanks in advance.

Upvotes: 0

Views: 1234

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You need a calendar table and left join

;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

Upvotes: 2

Related Questions