SQL display months when Count is 0

Question

I was wondering if someone could help me...

I have the following SQL query (have shortened this down as its a large union query)

  SELECT [  Month   ],
sum(total) 
 from
(select datename(month,Resolved1Date) as '  Month   ',
COUNT(case when 
fileDescription not like 'test%' 
and Issue1Description ='Escalated' then 0 else 1 end)   as 'total'   
FROM         complaint_1 WITH (nolock) INNER JOIN
             Case WITH (nolock) ON Case.ref = complaint_1.ref 
WHERE     
Resolved1Date >=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
Resolved1Date  <= dateadd(mm,datediff(mm,0,getdate()),0)
group by datename(month,Resolved1Date), datepart(month, Resolved1Date)
)x                     
group by  [  Month   ]
order by  [  Month   ] desc

the query counts all cases where the resolved date is between 1st day of current year and less than current month. my problem is if there are no results for a month it excludes the month, I would like my results to return something like:-

jan   5
feb   10
march 7
apr   0
may   2

can anyone guide me in the right direction?

Answer 1

Create a set of Months as the first table in the from clause, and join your query to this. Then you will get a result for every month. I have similar issues with financial reporting where I need results for all months and financial years. I have used the DATENAME function to ensure consistent results with your query. If you want data in Month order (Jan - Feb - Mar) you may not want to order by Month as this would be alphabetical, you would need to include a sort field.

SELECT M.[  Month  ] AS [  Month  ]
      ,SUM(ISNULL(x.total,0)) AS [Total] -- x.total will be null for months with no transactions.
  FROM -- Set of Months (need one record for each month)
     (SELECT * FROM (VALUES(DATENAME(month,'2015-01-01'),1)
                          ,(DATENAME(month,'2015-02-01'),2)
                          ,(DATENAME(month,'2015-03-01'),3)
                          ,(DATENAME(month,'2015-04-01'),4)
                          ,(DATENAME(month,'2015-05-01'),5)
                          ,(DATENAME(month,'2015-06-01'),6)
                          ,(DATENAME(month,'2015-07-01'),7)
                          ,(DATENAME(month,'2015-08-01'),8)
                          ,(DATENAME(month,'2015-09-01'),9)
                          ,(DATENAME(month,'2015-10-01'),10)
                          ,(DATENAME(month,'2015-11-01'),11)
                          ,(DATENAME(month,'2015-12-01'),12)) AS Mnth("  Month  ",MnthSort)) AS M
LEFT OUTER JOIN  -- Your from clause goes here.
     (SELECT * 
        FROM (VALUES (DATENAME(month,'2015-01-01'),5)
                    ,(DATENAME(month,'2015-02-01'),4)
                    ,(DATENAME(month,'2015-02-01'),6)
                    ,(DATENAME(month,'2015-03-01'),7)
                    ,(DATENAME(month,'2015-04-01'),0)
                    ,(DATENAME(month,'2015-05-01'),1)
                    ,(DATENAME(month,'2015-05-01'),1)
              ) AS data("  Month  ","total")) x ON x.[  Month  ] = M.[  Month  ]
GROUP BY M.[  Month  ], M.MnthSort
ORDER BY M.MnthSort

I ran this on SQL Server 2008 - R1

The first part of the from clause in the query defines the set of months in a table format with one row returned for each month (Run this to see results):

SELECT * FROM (VALUES(DATENAME(month,'2015-01-01'),1)
                     ,(DATENAME(month,'2015-02-01'),2)
                     ,(DATENAME(month,'2015-03-01'),3)
                     ,(DATENAME(month,'2015-04-01'),4)
                     ,(DATENAME(month,'2015-05-01'),5)
                     ,(DATENAME(month,'2015-06-01'),6)
                     ,(DATENAME(month,'2015-07-01'),7)
                     ,(DATENAME(month,'2015-08-01'),8)
                     ,(DATENAME(month,'2015-09-01'),9)
                     ,(DATENAME(month,'2015-10-01'),10)
                     ,(DATENAME(month,'2015-11-01'),11)
                     ,(DATENAME(month,'2015-12-01'),12)) AS Mnth("  Month  ",MnthSort)

The LEFT OUTER JOIN after it is to link the results of your query to each month, so each month gets a total. An outer join is used because there isn't a total for every month.

The Query using your sql from above would be like:

SELECT M.[  Month  ] AS [  Month  ]
      ,SUM(ISNULL(x.total,0)) AS [Total] -- x.total will be null for months with no transactions.
  FROM -- Set of Months (January - December), ensures one record for each month
     (SELECT * FROM (VALUES(DATENAME(month,'2015-01-01'),1)
                          ,(DATENAME(month,'2015-02-01'),2)
                          ,(DATENAME(month,'2015-03-01'),3)
                          ,(DATENAME(month,'2015-04-01'),4)
                          ,(DATENAME(month,'2015-05-01'),5)
                          ,(DATENAME(month,'2015-06-01'),6)
                          ,(DATENAME(month,'2015-07-01'),7)
                          ,(DATENAME(month,'2015-08-01'),8)
                          ,(DATENAME(month,'2015-09-01'),9)
                          ,(DATENAME(month,'2015-10-01'),10)
                          ,(DATENAME(month,'2015-11-01'),11)
                          ,(DATENAME(month,'2015-12-01'),12)) AS Mnth("  Month  ",MnthSort)) AS M
LEFT OUTER JOIN  -- Your Query included from here...
    (SELECT datename(month,Resolved1Date) as '  Month   ',
            COUNT(CASE WHEN fileDescription NOT LIKE 'test%' 
                        AND Issue1Description ='Escalated' THEN 0 ELSE 1
                  END) as 'total'   
       FROM complaint_1 WITH (nolock)
            INNER JOIN Case WITH (nolock) ON Case.ref = complaint_1.ref 
      WHERE     
            Resolved1Date >=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
            Resolved1Date  <= dateadd(mm,datediff(mm,0,getdate()),0)
      group by datename(month,Resolved1Date), datepart(month, Resolved1Date)
     ) x on x.[  Month  ] = M.[  Month  ]

GROUP BY M.[  Month  ], M.MnthSort
ORDER BY M.MnthSort