SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 If COUNT returns null value then display 0
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lauren_e
Starting Member

United Kingdom
1 Posts

Posted - 02/11/2014 :  09:33:05  Show Profile  Reply with Quote
I am developing a report to pull information out of a database using the Visual Studio Business Intellegence Studio.

I have a query to count the number of rows in a specific date range, however, if there are no records in the date range it does not display anything at all but I need it to display 0.

This is the query:
SELECT COUNT(OpportunityId) AS NoSalesWon
FROM OpportunityBase
WHERE (CreatedOn BETWEEN CAST(DATEADD(dd, - 7, GETDATE()) AS DATE) AND CAST(DATEADD(dd, 0, GETDATE()) AS DATE))
GROUP BY StatusCode
HAVING (StatusCode = 1)
UNION ALL
SELECT 0 AS Expr1
WHERE (NOT EXISTS
(SELECT OpportunityId
FROM OpportunityBase AS OpportunityBase_1
WHERE (StatusCode = 2)))



This returns a value of 0 if there are no records counted - Great :D
However, if there are records counted it display the number of records as well as a 0 in the row underneath which I don't want.

Is there anyway to stop this happening?

hbadministrator
Posting Yak Master

118 Posts

Posted - 02/20/2014 :  14:48:56  Show Profile  Reply with Quote
SELECT COUNT(OpportunityId) AS NoSalesWon
FROM OpportunityBase
WHERE ISNULL((CreatedOn BETWEEN CAST(DATEADD(dd, - 7, GETDATE()) AS DATE) AND CAST(DATEADD(dd, 0, GETDATE()),0) AS DATE))
GROUP BY StatusCode
HAVING (StatusCode = 1)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000