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?

Can you not just add another WHERE clause for Resolved1Date = 0? — nik0lai, Jun 01 '15 at 13:30
You should format your query. You should also fix the logic. For instance, the `case` statement in the `count(case)` isn't doing anything. You should also tag the question with the database you are using. — Gordon Linoff, Jun 01 '15 at 13:43
removing the case statement doesn't help with what i'm after,i think the issue is around the count as im counting a date field and then stating where the sate field is between two values, I need it to display a month as 0 if no matches. — sql2015, Jun 01 '15 at 15:01

nxb · Accepted Answer · 2015-06-02T14:32:00.933

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

thanks for your post @nxb, could you explain what you mean by "Create a set of Months as the first table in the from clause" im fairly new to SQL and this is starting to send me mad! — sql2015, Jun 02 '15 at 14:17
ahh got it, I had commented out the 'Left outer join" and put my query in, and was receiving incorrect syntax messages. I didn't understand it was used to link result of my query to months, makes sense now. thank you for your help @nxb :-) really appreciated — sql2015, Jun 02 '15 at 14:40

score 1 · Answer 2 · edited Jun 03 '15 at 11:10

1

You can create the temporary table and left join from that.

Something like this:

DECLARE @Helper TABLE 
(
  TheDate datetime
)

DECLARE @StartDate datetime
SELECT @StartDate = '01.01.2015'

WHILE @StartDate < DATEADD(day,7,GETDATE())
BEGIN
  INSERT INTO @Helper (Thedate) VALUES (@StartDate)

  SELECT @StartDate = DATEADD(MONTH, 1, @StartDate)

END

I hope it helps.

edited Jun 03 '15 at 11:10

Indian

529
1
12
25

answered Jun 01 '15 at 14:00

Exxo

11
1

SQL display months when Count is 0

2 Answers2