September 10, 2012 at 7:41 pm
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
September 10, 2012 at 7:48 pm
For ID = 3, the answer should be 2 not 3, right?
September 10, 2012 at 7:53 pm
Yes 2, sorry
September 10, 2012 at 8:19 pm
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)
September 10, 2012 at 8:22 pm
SS thanks
September 11, 2012 at 1:39 am
sogou34 (9/10/2012)
-------- http://www.chic-goods.us/ -----------hello,This is a wholesaler's web site.
---blah blah
Reported.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 12, 2012 at 7:11 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 12, 2012 at 8:53 pm
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.
September 12, 2012 at 8:57 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 12, 2012 at 9:20 pm
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
September 12, 2012 at 9:24 pm
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.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 12, 2012 at 9:53 pm
If all three are zero, desired result would be zero.
September 12, 2012 at 9:54 pm
texpic (9/12/2012)
If all three are zero, desired result would be zero.
What if all 3 are negative? Still zero?
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 13, 2012 at 2:31 am
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)
😉
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
September 13, 2012 at 4:55 am
ChrisM@Work (9/13/2012)
SELECTId, 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!
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply