Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Minimum Value 3 Fields > 0
Add to briefcase
17 posts, Page 2 of 2
««12

Minimum Value 3 Fields > 0 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
dwain.c
Posted Wednesday, September 12, 2012 9:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
Ah yes, NULLIF... Forgot about that one.

That will work but we still need to hear from the OP their preferred return result for that case so it can be plugged into your version of my query.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1358314
texpic
Posted Wednesday, September 12, 2012 9:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:35 PM
Points: 225, Visits: 263
If all three are zero, desired result would be zero.


Post #1358321
dwain.c
Posted Wednesday, September 12, 2012 9:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
texpic (9/12/2012)
If all three are zero, desired result would be zero.


What if all 3 are negative? Still zero?

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1358323
ChrisM@Work
Posted Thursday, September 13, 2012 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990 
SELECT 
	Id, A, B, C, 
	MinVal 
FROM #MinAmt
CROSS APPLY (
	SELECT MIN(a) 
	FROM (VALUES (A),(B),(C) ) v (a) 
	WHERE a > 0
) x (MinVal)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1358396
dwain.c
Posted Thursday, September 13, 2012 4:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
ChrisM@Work (9/13/2012)
SELECT 
	Id, A, B, C, 
	MinVal 
FROM #MinAmt
CROSS APPLY (
	SELECT MIN(a) 
	FROM (VALUES (A),(B),(C) ) v (a) 
	WHERE a > 0
) x (MinVal)



And the referee throws down a red penalty flag!

This is the SQL 2005 forum!

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1358469
ChrisM@Work
Posted Thursday, September 13, 2012 5:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990
dwain.c (9/13/2012)
ChrisM@Work (9/13/2012)
SELECT 
	Id, A, B, C, 
	MinVal 
FROM #MinAmt
CROSS APPLY (
	SELECT MIN(a) 
	FROM (VALUES (A),(B),(C) ) v (a) 
	WHERE a > 0
) x (MinVal)



And the referee throws down a red penalty flag!

This is the SQL 2005 forum!


Challenged! texpic always posts in the 2K8 forum!

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1358476
raghavatgk
Posted Thursday, September 13, 2012 6:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:00 AM
Points: 38, Visits: 249
Try This



select Id,MIN(a) from (
select a.id, a.a from #MinAmt a ,#MinAmt

union
select a.id, a.b from #MinAmt a ,#MinAmt
union
select a.id, a.c from #MinAmt a ,#MinAmt
) a where a>0 group by Id
Post #1358512
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.