Post reply

a query displaying 0 instead of not showing

  • November 17, 2019 at 1:25 pm

    #3698325

    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

    #3698326

    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;
    Attachments:
    You must be logged in to view attached files.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply