Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.
 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
52326 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
52326 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  
 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