Show emplty output with 0

Question

Show emplty output with 0

karthik82.vk

SSCrazy

Points: 2002
More actions
October 7, 2014 at 12:11 am

#297187

Hi,
I have a sql query that gets the count of exams held in each month. In every month there can be some exams happening or may not happening.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
I tried using case but it does not work.
Can any one help me with this?
Regards,
Karthik Venkatraman

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

karthik82.vk (10/7/2014)
Hi,
I have a sql query that gets the count of exams held in each month. In every month there can be some exams happening or may not happening.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when we dnt have any exams for the current month it has to show 0 as the result.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So instead of that empty output i need to show o in both the columns.
I tried using case but it does not work.
Can any one help me with this?
Regards,
Karthik Venkatraman

If rows don't exist for the period you are interested in, then you can create them:

DECLARE @TheDate DATE

SET @TheDate = '2014-05-19 00:00:00.000'

SELECT TheYear = YEAR(@TheDate), TheMonth = MONTH(@TheDate)

Then it's a simple matter of left-joining your data to this table source:

DECLARE @TheDate DATE

SET @TheDate = '2014-05-19 00:00:00.000'

;WITH TestData (EXAMID, EXAMCATEGORY, EXOCCRDATE) AS (

SELECT 'TEST 1', 'Major', DATEADD(DAY,-2,41805) UNION ALL

SELECT 'TEST 2', 'Critical', DATEADD(DAY,-2,41814) UNION ALL

SELECT 'TEST 3', 'Major', DATEADD(DAY,-2,41778) UNION ALL

SELECT 'TEST 4', 'Major', DATEADD(DAY,-2,41763) UNION ALL

SELECT 'TEST 5', 'Major', DATEADD(DAY,-2,41829) UNION ALL

SELECT 'TEST 6', 'Major', DATEADD(DAY,-2,41815) UNION ALL

SELECT 'TEST 7', 'Major', DATEADD(DAY,-2,41882) UNION ALL

SELECT 'TEST 8', 'Major', DATEADD(DAY,-2,41723) UNION ALL

SELECT 'TEST 9', 'Major', DATEADD(DAY,-2,41733) UNION ALL

SELECT 'TEST 10', 'Critical', DATEADD(DAY,-2,41766) UNION ALL

SELECT 'TEST 11', 'Major', DATEADD(DAY,-2,41789) UNION ALL

SELECT 'TEST 12', 'Major', DATEADD(DAY,-2,41735) UNION ALL

SELECT 'TEST 13', 'Major', DATEADD(DAY,-2,41763) UNION ALL

SELECT 'TEST 14', 'Critical', DATEADD(DAY,-2,41801) UNION ALL

SELECT 'TEST 15', 'Normal', DATEADD(DAY,-2,41900) UNION ALL

SELECT 'TEST 16', 'Normal', DATEADD(DAY,-2,41874) UNION ALL

SELECT 'TEST 17', 'Critical', DATEADD(DAY,-2,41850) UNION ALL

SELECT 'TEST 18', 'Critical', DATEADD(DAY,-2,41684) UNION ALL

SELECT 'TEST 19', 'Major', DATEADD(DAY,-2,41887) UNION ALL

SELECT 'TEST 20', 'Major', DATEADD(DAY,-2,41748) UNION ALL

SELECT 'TEST 21', 'Major', DATEADD(DAY,-2,41728) UNION ALL

SELECT 'TEST 22', 'Major', DATEADD(DAY,-2,41764) UNION ALL

SELECT 'TEST 23', 'Major', DATEADD(DAY,-2,41776) UNION ALL

SELECT 'TEST 24', 'Critical', DATEADD(DAY,-2,41763) UNION ALL

SELECT 'TEST 25', 'Normal', DATEADD(DAY,-2,41886) UNION ALL

SELECT 'TEST 26', 'Major', DATEADD(DAY,-2,41893) UNION ALL

SELECT 'TEST 27', 'Critical', DATEADD(DAY,-2,41764) UNION ALL

SELECT 'TEST 28', 'Major', DATEADD(DAY,-2,41803) UNION ALL

SELECT 'TEST 29', 'Normal', DATEADD(DAY,-2,41850) UNION ALL

SELECT 'TEST 30', 'Major', DATEADD(DAY,-2,41724) UNION ALL

SELECT 'TEST 31', 'Normal', DATEADD(DAY,-2,41846) UNION ALL

SELECT 'TEST 32', 'Major', DATEADD(DAY,-2,41832) UNION ALL

SELECT 'TEST 33', 'Major', DATEADD(DAY,-2,41793) UNION ALL

SELECT 'TEST 34', 'Normal', DATEADD(DAY,-2,41876) UNION ALL

SELECT 'TEST 35', 'Normal', DATEADD(DAY,-2,41773) UNION ALL

SELECT 'TEST 36', 'Critical', DATEADD(DAY,-2,41760) UNION ALL

SELECT 'TEST 37', 'Critical', DATEADD(DAY,-2,41894) UNION ALL

SELECT 'TEST 38', 'Major', DATEADD(DAY,-2,41722) UNION ALL

SELECT 'TEST 39', 'Critical', DATEADD(DAY,-2,41858) UNION ALL

SELECT 'TEST 40', 'Major', DATEADD(DAY,-2,41885) UNION ALL

SELECT 'TEST 41', 'Major', DATEADD(DAY,-2,41846) UNION ALL

SELECT 'TEST 42', 'Major', DATEADD(DAY,-2,41736) UNION ALL

SELECT 'TEST 43', 'Major', DATEADD(DAY,-2,41748) UNION ALL

SELECT 'TEST 44', 'Major', DATEADD(DAY,-2,41726) UNION ALL

SELECT 'TEST 45', 'Critical', DATEADD(DAY,-2,41899) UNION ALL

SELECT 'TEST 46', 'Normal', DATEADD(DAY,-2,41685) UNION ALL

SELECT 'TEST 47', 'Normal', DATEADD(DAY,-2,41893) UNION ALL

SELECT 'TEST 48', 'Normal', DATEADD(DAY,-2,41736) UNION ALL

SELECT 'TEST 49', 'Major', DATEADD(DAY,-2,41748) )

SELECT

d.TheYear,

d.TheMonth,

Examid = ISNULL(t.Examid, 'None'),

CumCount = count(t.Examid)

FROM (SELECT TheYear = YEAR(@TheDate), TheMonth = MONTH(@TheDate)) d

LEFT JOIN TestData t

ON Year(t.EXOCCRDATE) = d.TheYear

AND Month(t.EXOCCRDATE) = d.TheMonth

AND t.ExamCategory IN ('Major','Critical')

GROUP BY t.Examid, d.TheYear, d.TheMonth

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik82.vk SSCrazy Points: 2002 More actions · Answer 2

Hi Chris,

I cannot create rows for the missing dates or change the date as its not a permitted- one. the data comes from another system and adding or changing spoils the integrity of original data.

Is there any other way for this....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

karthik82.vk (10/7/2014)
Hi Chris,
I cannot create rows for the missing dates or change the date as its not a permitted- one. the data comes from another system and adding or changing spoils the integrity of original data.
Is there any other way for this....

Spend a little time on the code I posted, Karthik. Then, if you're still not sure what I mean by "create a dat"e, post back

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

karthik82.vk (10/7/2014)
Hi Chris,
I cannot create rows for the missing dates or change the date as its not a permitted- one. the data comes from another system and adding or changing spoils the integrity of original data.
Is there any other way for this....

Did you read the sample code provided by Chris?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

karthik82.vk SSCrazy Points: 2002 More actions · Answer 5

Hi Chris,

Thanks for your workaround.

Your code has helped me to solve my problem with few changes.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

You're welcome. Can you post up what you have? There may be improvements / better explanations.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden