best way to get amount from nulls

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 2012 Forums

Transact-SQL (2012)

best way to get amount from nulls

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-02-20 : 15:07:52

i have a query

select percentoffminimum,freeshippingminimum,amountoffminimum from coupons

now for each of these the value can be Null, 0 or with a value
I need to query if any of these are not null or 0 -- I want to get the highest minimum

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 15:15:52

See if one of the 3 queries in the below example will work for you:

CREATE TABLE #tmp (id INT);
INSERT INTO #tmp VALUES (1),(2),(NULL),(0);

SELECT MIN(id) FROM #tmp; -- gives 0
SELECT MAX(id) FROM #tmp; -- gives 2
SELECT MIN(id) FROM #tmp WHERE id <> 0; -- gives 1

DROP TABLE #tmp;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-20 : 15:16:26

I'm not sure hwo to respond. You have over 1300 posts and no sample data? What do you want if a column doens't have any data that is NOT Null or 0? Or is that even possible? What is a "highest minimum?"

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-02-20 : 16:08:27

I want the max of the 3 fields -- not max of 1 field
would that be with a union?

heres sample data

percentoffminimum freeshippingminimum amountoffminimum
NULL NULL NULL
NULL NULL 50.00
NULL 50.00 NULL
NULL NULL NULL
0.00 NULL 25.00

from the above the first should return 0 (all nulls), second 50, third 50,4th 0 , 5th 25

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-02-20 : 16:25:50

This??? Not Sure

Select MAX(A)
from
(
Select percentoffminimum A from Coupons
union
Select freeshippingminimum  from Coupons
union
Select amountoffminimum from Coupons
)P

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-20 : 17:37:05

Esthera, since you are a "Flowing Fount of Yak Knowledge," I'd assume you know how to post data in a consumable format. Either my assumption is incorrect or you are trying to make it hard for us to help you. So, please read the links I posted above on how to ask your questions on a SQL forum.

Be that as it may, here is a solution for ya:

DECLARE @Foo TABLE (percentoffminimum NUMERIC(18,4), freeshippingminimum NUMERIC(18,4), amountoffminimum NUMERIC(18,4))

INSERT @Foo
VALUES
(NULL, NULL, NULL),
(NULL, NULL, 50.00),
(NULL, 50.00, NULL),
(NULL, NULL, NULL),
(0.00, NULL, 25.00) 

SELECT
	(
		SELECT COALESCE(MAX(P), 0.00)
		FROM 
		(
			VALUES 
				(percentoffminimum), 
				(freeshippingminimum), 
				(amountoffminimum)
		) AS Foo(P)
	) AS SomeUnspecifiedColumnName
FROM
	@Foo AS F

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 23:45:59

May this this???

DECLARE @tab TABLE( percentoffminimum DEC(5,2), freeshippingminimum DEC(5,2), amountoffminimum DEC(5,2))
INSERT INTO @tab
SELECT NULL, NULL, NULL union all
SELECT NULL, NULL, 50.00 union all
SELECT NULL, 50.00, NULL union all
SELECT NULL, NULL, NULL union all
SELECT 0.00, NULL, 25.00
--from the above the first should return 0 (all nulls), second 50, third 50,4th 0 , 5th 25

SELECT COALESCE( NULLIF(percentoffminimum, 0), NULLIF(freeshippingminimum, 0), NULLIF(amountoffminimum, 0), 0)
FROM @tab

EDIT : Don't mind this post.. I have posted this by seeing the explanation only(from the above the first should return 0 (all nulls), second 50, third 50,4th 0 , 5th 25 -- Posted by Esthera at 02/20/2013 : 16:08:27).. I think Lamprey's post is working fine as per your need

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources