Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Minimum Value 3 Fields > 0 Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 7:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323
This seems like it should be easy but I can't seem to come up with a simple formula. Need the minimum value where answer > 0.

Result:


1 = 1
2 = 4
3 = 3
4 = 1
5 = 2
6 = 1

CREATE TABLE #MinAmt
(Id int, A int, B int, C int)

INSERT INTO #MinAmt
(Id,A,B,C)
SELECT 1,1,2,3 UNION ALL
SELECT 2,4,5,6 UNION ALL
SELECT 3,0,2,3 UNION ALL
SELECT 4,1,2,0 UNION ALL
SELECT 5,-1,2,3 UNION ALL
SELECT 6,1,2,-3






Post #1357088
Posted Monday, September 10, 2012 7:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:37 PM
Points: 2,262, Visits: 5,436
For ID = 3, the answer should be 2 not 3, right?
Post #1357090
Posted Monday, September 10, 2012 7:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323
Yes 2, sorry


Post #1357091
Posted Monday, September 10, 2012 8:19 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:36 PM
Points: 3,118, Visits: 11,556
select
ID,
[MinVal] = (
select
min(x)
from
(
select x=A where A > 0 union all
select x=B where B > 0 union all
select x=C where C > 0
) m
)
from
#MinAmt
order by
ID

Results:
ID          MinVal
----------- -----------
1 1
2 4
3 2
4 1
5 2
6 1

(6 row(s) affected)
Post #1357094
Posted Monday, September 10, 2012 8:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:10 AM
Points: 235, Visits: 323
SS thanks


Post #1357096
Posted Tuesday, September 11, 2012 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 5,317, Visits: 12,361
sogou34 (9/10/2012)

-------- http://www.chic-goods.us/ -----------

hello,This is a wholesaler's web site.

---blah blah


Reported.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1357177
Posted Wednesday, September 12, 2012 7:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
Another way:

;WITH MyValues AS (
SELECT Id
,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END
,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END
,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END
FROM #MinAmt)
SELECT Id
,MinAmt=CASE
WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END
WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END
ELSE CASE WHEN C < A THEN C ELSE A END END
FROM MyValues


BTW. You didn't specify what to return if all values are <= 0.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1358288
Posted Wednesday, September 12, 2012 8:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:36 PM
Points: 3,118, Visits: 11,556
dwain.c (9/12/2012)
Another way:

;WITH MyValues AS (
SELECT Id
,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END
,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END
,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END
FROM #MinAmt)
SELECT Id
,MinAmt=CASE
WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END
WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END
ELSE CASE WHEN C < A THEN C ELSE A END END
FROM MyValues


BTW. You didn't specify what to return if all values are <= 0.


You also need to deal with the case where the values are null.

You code returns 2147483647 when they are all <1 or null, and I doubt that is what they would want.

Returning a null probably makes the most sense for that case.

Post #1358306
Posted Wednesday, September 12, 2012 8:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
Michael Valentine Jones (9/12/2012)
dwain.c (9/12/2012)
Another way:

;WITH MyValues AS (
SELECT Id
,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END
,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END
,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END
FROM #MinAmt)
SELECT Id
,MinAmt=CASE
WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END
WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END
ELSE CASE WHEN C < A THEN C ELSE A END END
FROM MyValues


BTW. You didn't specify what to return if all values are <= 0.


You also need to deal with the case where the values are null.

You code returns 2147483647 when they are all <1 or null, and I doubt that is what they would want.

Returning a null probably makes the most sense for that case.



That's why I asked what the OP wanted for that case. Easy enough to wrap another CASE around the whole result like:

CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END


Or if that seems too messy, put my CASE into a CROSS APPLY and just return that result to this latest CASE.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1358309
Posted Wednesday, September 12, 2012 9:20 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:36 PM
Points: 3,118, Visits: 11,556
dwain.c (9/12/2012)
Michael Valentine Jones (9/12/2012)
dwain.c (9/12/2012)
Another way:

;WITH MyValues AS (
SELECT Id
,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END
,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END
,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END
FROM #MinAmt)
SELECT Id
,MinAmt=CASE
WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END
WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END
ELSE CASE WHEN C < A THEN C ELSE A END END
FROM MyValues


BTW. You didn't specify what to return if all values are <= 0.


You also need to deal with the case where the values are null.

You code returns 2147483647 when they are all <1 or null, and I doubt that is what they would want.

Returning a null probably makes the most sense for that case.



That's why I asked what the OP wanted for that case. Easy enough to wrap another CASE around the whole result like:

CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END


Or if that seems too messy, put my CASE into a CROSS APPLY and just return that result to this latest CASE.


Wrapping it in NULLIF([longer case],2147483647) would be simpler.

Of course, if one of the values in the table was actually 2147483647, it would return a null, so you might need extra code to deal with that:

WITH MyValues AS (
SELECT Id
,A=CASE WHEN A > 0 THEN A ELSE 3000000000000 END
,B=CASE WHEN B > 0 THEN B ELSE 3000000000000 END
,C=CASE WHEN C > 0 THEN C ELSE 3000000000000 END
FROM #MinAmt)
SELECT Id
,MinAmt=
convert(int,nullif(
CASE
WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END
WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END
ELSE CASE WHEN C < A THEN C ELSE A END END
,3000000000000))
FROM MyValues




Post #1358311
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse