Return 0 ONLY IF NO records are present.

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return 0 ONLY IF NO records are present.

Author

Topic

BKSwindell
Starting Member

6 Posts

Posted - 2013-11-05 : 07:59:01

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

52326 Posts

Posted - 2013-11-05 : 08:08:27

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

BKSwindell
Starting Member

6 Posts

Posted - 2013-11-05 : 08:13:59

Thank you for your response, I will give that a try. I knew I was missing a simple solution.

BKSwindell
Starting Member

6 Posts

Posted - 2013-11-05 : 08:25:58

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

BKSwindell
Starting Member

6 Posts

Posted - 2013-11-05 : 08:38:05

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 08:47:46

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources