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