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 2012 Forums
 Transact-SQL (2012)
 best way to get amount from nulls
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 02/20/2013 :  15:07:52  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/20/2013 :  15:15:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/20/2013 :  15:16:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 02/20/2013 :  16:08:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/20/2013 :  16:25:50  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/20/2013 :  17:37:05  Show Profile  Reply with Quote
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

Edited by - Lamprey on 02/20/2013 17:44:55
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 02/20/2013 :  23:45:59  Show Profile  Reply with Quote
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

Edited by - bandi on 02/21/2013 00:18:55
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