CREATE TABLE #MinAmt(Id int, A int, B int, C int)INSERT INTO #MinAmt(Id,A,B,C)SELECT 1,1,2,3 UNION ALLSELECT 2,4,5,6 UNION ALLSELECT 3,0,2,3 UNION ALLSELECT 4,1,2,0 UNION ALLSELECT 5,-1,2,3 UNION ALLSELECT 6,1,2,-3
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 #MinAmtorder by ID
ID MinVal----------- -----------1 12 43 24 15 26 1(6 row(s) affected)
;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
CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END
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