Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Sql graph- return months with no data

Posted on 2008-10-15
Medium Priority
420 Views
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.
0
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

LVL 5

Accepted Solution

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

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

ID: 22727254
Many, many thanks for your help
0

LVL 5

Expert Comment

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

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

## Featured Post

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month8 days, left to enroll

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

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