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;
Go to Top of Page

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
Go to Top of Page

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


Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -