0

I want to show data for all the months even if there is no data for that month. The code below is not working...

;with months (mon) as
    (select mon from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) a (mon))
SELECT c.CASE_NBR, c.SEVERITY_OF_COLLISION_ID, c.OCCURENCE_TIMESTAMP, DATEPART(MONTH, c.OCCURENCE_TIMESTAMP) AS month1, 
    CASE WHEN c.SEVERITY_OF_COLLISION_ID = '4' THEN 'Fatal Collisions' WHEN c.SEVERITY_OF_COLLISION_ID = '5' THEN 'Non-Fatal Collisions' END AS coll_severity_type, 
    CASE WHEN months.mon = '1' THEN 'January' 
         WHEN months.mon = '2' THEN 'February' 
         WHEN months.mon = '3' THEN 'March'
         WHEN months.mon = '4' THEN 'April' 
         WHEN months.mon = '5' THEN 'May' 
         WHEN months.mon = '6' THEN 'June' 
         WHEN months.mon = '7' THEN 'July' 
         WHEN months.mon = '8' THEN 'August'
         WHEN months.mon = '9' THEN 'September' 
         WHEN months.mon = '10' THEN 'October' 
         WHEN months.mon = '11' THEN 'November' 
         WHEN months.mon = '12' THEN 'December' 
     END AS month_name
FROM ECRDBA.COLLISIONS AS c
    left join months on months.mon = DATEPART(MONTH, c.OCCURENCE_TIMESTAMP)
    INNER JOIN ECRDBA.CL_OBJECTS AS o ON o.COLLISION_ID = c.ID 
    INNER JOIN ECRDBA.OBJECT_TYPES AS ot ON ot.ID = o.OBJECT_TYPE_ID 
where (c.SEVERITY_OF_COLLISION_ID NOT IN ('6')) AND (c.CASE_YEAR IN (2016)) and (ot.CODE = '06')
belwood
  • 3,320
  • 11
  • 38
  • 45
user11512406
  • 11
  • 1
  • 1
    "its not working" is not a description of a problem nor a question. [Edit] your post and include the tables' structure (as `CREATE TABLE` statements), sample data (as `INSERT` statements) and the result you expect with that sample data. Don't forget to tag the actual DBMS you're using. – sticky bit May 16 '19 at 21:38
  • Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – philipxy May 16 '19 at 21:58
  • Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy May 16 '19 at 21:58
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy May 16 '19 at 21:59
  • Removed distracting wording, improved code formatting. – belwood May 17 '19 at 00:20
  • Please, that's enough "it's not working". – philipxy May 17 '19 at 04:06

2 Answers2

0

Your query clause should look like:

SELECT . . .
FROM months LEFT JOIN
     ECRDBA.COLLISIONS c 
     ON months.mon = DATEPART(MONTH, c.OCCURENCE_TIMESTAMP) AND
        c.SEVERITY_OF_COLLISION_ID NOT IN ('6') AND
        c.CASE_YEAR IN (2016) LEFT JOIN
     ECRDBA.CL_OBJECTS o
     ON o.COLLISION_ID = c.ID LEFT JOIN
     ECRDBA.OBJECT_TYPES ot
     ON ot.ID = o.OBJECT_TYPE_ID AND ot.CODE = '06'

You need to start with the table that you want to keep all the rows. Then the subsequent joins should be left joins and the conditions on those tables should be in the on clause, not the where.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Just start from Month table as shown below and you should get your expected result.

 FROM months
 LEFT JOIN  ECRDBA.COLLISIONS AS c
     ON months.mon = DATEPART(MONTH, c.OCCURENCE_TIMESTAMP)
 ......
mkRabbani
  • 16,295
  • 2
  • 15
  • 24