• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Sql graph- return months with no data

We are trying to produce a trend graph for each month in a year  returning the number of helpdesk calls for each category. For some categories, not all months will contain data (e.g. Feb may return a result while March had no incidents for this category).
Currently, when the graph is generated, any months in the category which have no data do not appear on the graph (e.g Jan, Feb, April). This leads to a misleading trend chart as the months with no data do not appear.
A crosstab report fills the empty cells with a 0, which is fine, but we need to represent these in chart format also.
Suggestions please?
0
Ian_Ross_Walker
Asked:
Ian_Ross_Walker
  • 3
  • 2
1 Solution
 
jfmadorCommented:
You can use sum of case to get your information, then you will get a column with the number of incident for each month

SELECT Category, DatePart(yyyy, calldate) as year,
SUM(CASE WHEN DATEPART(m, CALLDATE) = 1 THEN 1 ELSE 0 END) as JanuaryCount,
SUM(CASE WHEN DATEPART(m, CALLDATE) = 2 THEN 1 ELSE 0 END) as februaryCount,
SUM(CASE WHEN DATEPART(m, CALLDATE) = 3 THEN 1 ELSE 0 END) as MarchCount,
SUM(CASE WHEN DATEPART(m, CALLDATE) = 4 THEN 1 ELSE 0 END) as AprilCount,
...
SUM(CASE WHEN DATEPART(m, CALLDATE) = 12 THEN 1 ELSE 0 END) as DecemberCount
FROM CALL
GROUP BY Categor, DatePart(yyyy, calldate)
0
 
jfmadorCommented:
Sorry I forget the "y" to Category in the group statement

Also using this method you will get column with 0 as the number, as you mention for the month of march
0
 
Ian_Ross_WalkerAuthor Commented:
Many, many thanks for your help
0
 
jfmadorCommented:
Hello Ian, I just want to remind you that if my answer works you can close the question

Thank you
0
 
Ian_Ross_WalkerAuthor Commented:
Thanks for all your help..much appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now