Post reply

Display all months even if no data available

  • May 15, 2019 at 4:24 pm

    #3643950

    Hi, I am stuck on a query where I need to display all 12 months of data. If there is no data available for a particular month, it should show it as 0 instead of missing that one out completely. Below is the SQL code I am using.

    SELECT c.NBR,
           c.SEVERITY_OF_COLLISION_ID,
           c.OCCURENCE_TIMESTAMP,
           DATEPART(MONTH, c.OCCURENCE_TIMESTAMP) AS month1,
           CASE WHEN c.SVRTY_OF_CLLSON_ID = '4' THEN 'Ftl Cllsons'
                WHEN c.SVRTY_OF_CLLSON_ID = '5' THEN 'Nn-Ftl Cllsons'
           END AS coll_svrty_type
    FROM   DBA.CLLSONS AS c
           INNER JOIN DBA.CL_OBJECTS AS o ON o.CLLSON_ID = c.ID
           INNER JOIN DBA.OBJECT_TYPES AS ot ON ot.ID = o.OBJECT_TYPE_ID
    WHERE  (ot.CODE = '06') AND (c.SVRTY_OF_CLLSON_ID NOT IN ('6')) AND (c.CASE_YEAR IN (2016))

    Any help will be appreciated.

  • May 15, 2019 at 5:31 pm

    #3643980

    You can use an outer join to a fabricated "table" of months:

    with months (mon)
    as
    (select mon
     from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) a (mon)
    )

    This falls under the heading of a "Tally Table", which is used for these and several other kinds of problems.  There are fancier ways of making the tally table, but this is simple enough for this job.

  • May 16, 2019 at 8:21 pm

    #3644347

    works like a charm... thank you for your help.

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

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