Solved

Sql graph- return months with no data

Posted on 2008-10-15
5
409 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

914 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

13 Experts available now in Live!

Get 1:1 Help Now