Post reply

Monthly data

  • July 8, 2014 at 9:31 am

    #290699

    I am trying to bring in data for last 6 months . However , there is data only for May and June so the query brings in numbers for those months.

    I need to get zero if there are no numbers coming for the other months.

    How can I bring in all of last 6 months with numbers or zero otherwise.(Please let me know the logic)

    Here is my current query:

    SELECT FORMAT(OpenedTime, 'yyyy-MM') Year_Week

    ,g.Manager

    ,COUNT(Change_ID) AS Change_Count

    FROM Prod p

    LEFT JOIN Teams G

    ON p.Group = G.Name

    where OpenedTime > DATEADD(MM,-6,GETDATE())

    GROUP BY FORMAT(Opened_Time, 'yyyy-MM'),

    G.Manager

  • July 8, 2014 at 9:39 am

    #1727814

    Ideally, you would have a calendar table to join your data table. If you don't, you can build one on the fly with a CTE similar to a tally or numbers table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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