Solved

Sql graph- return months with no data

Posted on 2008-10-15
5
408 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
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
jfmador earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now