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 2005 Forums
 Transact-SQL (2005)
 Return 0 ONLY IF NO records are present.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BKSwindell
Starting Member

USA
6 Posts

Posted - 11/05/2013 :  07:59:01  Show Profile  Reply with Quote
Hello, what would be the best approach to return a record when no results are found. However if there are results the extra record must be filtered out.

I know I can UNION join a 0 record in but my problem is that when there are records found that 0 will be returned as well.

Example: Sales by Salesperson, this month. I want to return the aggregated sales for each salesperson from orders. Bob, $2000 / John, $3500 / Mary, $2850.

Now in the case of no sales that month I would like to return, (No Sales), $0

We also have the added restriction that logic must be contained with in a single Query, however that query may contain sub queries.

Thanks for the insight.

Brad Swindell

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  08:08:27  Show Profile  Reply with Quote

SELECT ..
FROM Yourquery
UNION ALL
SELECT 'NetSales' AS Name,0 AS Sales
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE <your current query conditions>
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

BKSwindell
Starting Member

USA
6 Posts

Posted - 11/05/2013 :  08:13:59  Show Profile  Reply with Quote
Thank you for your response, I will give that a try. I knew I was missing a simple solution.
Go to Top of Page

BKSwindell
Starting Member

USA
6 Posts

Posted - 11/05/2013 :  08:25:58  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


SELECT ..
FROM Yourquery
UNION ALL
SELECT 'NetSales' AS Name,0 AS Sales
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE <your current query conditions>
)


Worked perfectly. Thank you.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

BKSwindell
Starting Member

USA
6 Posts

Posted - 11/05/2013 :  08:38:05  Show Profile  Reply with Quote
How could I structure it like this? This code works but how can I move the filters all down the outer select? Our BI software need this structure. Thanks.

SELECT ISNULL(J.Salesperson_Id,0) AS 'Salesperson_Id'
,SUM(ISNULL(J.Amount,0)) AS 'Total_Amount'
FROM [Electronix_Final].[dbo].[OE_Job] J
FULL OUTER JOIN (SELECT 0 AS 'Salesperson_Id',0 AS 'Amount'
WHERE NOT EXISTS (SELECT 1 Salesperson_Id
FROM [Electronix_Final].[dbo].[OE_Job]
WHERE Sold_Date >= CONVERT(VARCHAR,DATEADD(D,-(DAY(DATEADD(M,-1,GETDATE()-2))),DATEADD(M,-1,GETDATE()-1)),101))
) SJ ON J.Salesperson_Id = SJ.Salesperson_Id
WHERE ((J.Sold_Date >= CONVERT(VARCHAR,DATEADD(D,-(DAY(DATEADD(M,-1,GETDATE()-2))),DATEADD(M,-1,GETDATE()-1)),101)
AND J.Amount > 0)
OR J.Sold_Date IS NULL)
GROUP BY ISNULL(J.Salesperson_Id,0)

Edited by - BKSwindell on 11/05/2013 08:44:08
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  08:47:46  Show Profile  Reply with Quote

SELECT ISNULL(J.Salesperson_Id,Default_Salesperson_Id) AS 'Salesperson_Id'
,ISNULL(J.Amount,Default_Amount) AS 'Amount'
FROM [Electronix_Final].[dbo].[OE_Job] J
FULL OUTER JOIN (SELECT 0 AS 'Default_Salesperson_Id',0 AS 'Default_Amount'
WHERE NOT EXISTS (SELECT 1 
FROM [Electronix_Final].[dbo].[OE_Job]
WHERE Sold_Date >= '11/1/2014')
) SJ ON 1=1
WHERE (J.Sold_Date >= '11/1/2014' OR J.Sold_Date IS NULL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.08 seconds. Powered By: Snitz Forums 2000