?
Solved

Sql graph- return months with no data

Posted on 2008-10-15
5
Medium Priority
?
414 Views
Last Modified: 2012-08-13
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
Comment
Question by:Ian_Ross_Walker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
jfmador earned 1500 total points
ID: 22727234
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
 
LVL 5

Expert Comment

by:jfmador
ID: 22727248
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
 

Author Comment

by:Ian_Ross_Walker
ID: 22727254
Many, many thanks for your help
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22733951
Hello Ian, I just want to remind you that if my answer works you can close the question

Thank you
0
 

Author Closing Comment

by:Ian_Ross_Walker
ID: 31506558
Thanks for all your help..much appreciated
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question