;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 ENDFROM MyValues

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.

;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 ENDFROM 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.

;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 ENDFROM 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 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.

;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 ENDFROM 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