November 17, 2019 at 1:25 pm
hello,
the following query is displaying the result i want except i want it to show 0 for each month with non production.
SELECT DATENAME(MONTH, DATEADD(M, MONTH(PolicyDetails.IssuedDate), - 1)) AS Month, SUM(PolicyDetails.Premium) AS TotalProduction, DATENAME(YEAR, PolicyDetails.IssuedDate) AS Year FROM PolicyDetails INNER JOIN Clients ON PolicyDetails.ClientId = Clients.ClientId WHERE (Clients.Username = @Username) GROUP BY MONTH(PolicyDetails.IssuedDate), DATENAME(YEAR, PolicyDetails.IssuedDate)
Month || Total Production -$$
2019 - August || 45.00
2019 - October || 45.00
in this table i want to show "2019 - September" with Total Production = 0 instead of displaying nothing. How ??
November 17, 2019 at 2:13 pm
You need a "table" with the months with the range of months.
drop table if exists sales;
create table sales(DateOfSale date, Price smallmoney);
insert into sales values('20190802', 45),('20191015', 45);
with months as(select mo from (values(8),(9),(10),(11)) mos(mo))
select COALESCE(SUM(s.Price), 0) total, m.mo MonthOfSale
from sales s
right join months m on DATEPART(mm, s.DateOfSale) = m.mo
group by mo;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy