Display all months even if no data available

  • msorabh

    SSC Enthusiast

    Points: 188

    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.

  • crow1969

    SSCrazy

    Points: 2732

    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.

  • msorabh

    SSC Enthusiast

    Points: 188

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

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

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