Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Minimum Value 3 Fields > 0 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 10, 2012 7:41 PM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, October 18, 2016 7:27 AM Points: 240, Visits: 356
 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 = 12 = 43 = 34 = 15 = 26 = 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 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 `
Post #1357088
 Posted Monday, September 10, 2012 7:48 PM
 SSCrazy Group: General Forum Members Last Login: Saturday, November 19, 2016 1:44 PM Points: 2,271, Visits: 5,545
 For ID = 3, the answer should be 2 not 3, right?
Post #1357090
 Posted Monday, September 10, 2012 7:53 PM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, October 18, 2016 7:27 AM Points: 240, Visits: 356
 Yes 2, sorry
Post #1357091
 Posted Monday, September 10, 2012 8:19 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 `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`Results:`ID MinVal----------- -----------1 12 43 24 15 26 1(6 row(s) affected)`
Post #1357094
 Posted Monday, September 10, 2012 8:22 PM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, October 18, 2016 7:27 AM Points: 240, Visits: 356
 SS thanks
Post #1357096
 Posted Tuesday, September 11, 2012 1:39 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 2:49 PM Points: 7,948, Visits: 19,043
Post #1357177
 Posted Wednesday, September 12, 2012 7:11 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 ENDFROM 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1358288
 Posted Wednesday, September 12, 2012 8:53 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 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 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.
Post #1358306
 Posted Wednesday, September 12, 2012 8:57 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 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 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1358309
 Posted Wednesday, September 12, 2012 9:20 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 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 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`
Post #1358311

 Permissions