Compare columns within the same row

  • I have the following row:

     

    And, I need to compare the Amts to only pull back the Reason for the largest Amt Value.  In the example above Amt2 has the largest values, so I would need to SELECT only Amt2Reason of YY-This2.

    I have been at this a few hours now and nothing is working out for me, is this possible?

     

    Any and all help will be greatly appreciated!

    • This topic was modified 4 months ago by  GBeezy.
  • The most flexible way is to use CROSS APPLY:

    SELECT d.ID, ca1.Amt, ca1.AmtReason
    FROM #data d
    CROSS APPLY (
    SELECT TOP (1) Amt, AmtReason
    FROM ( VALUES(d.amt1, d.amt1Reason), (d.amt2, d.amt2Reason), (d.amt3, d.amt3Reason) /*,...*/ ) AS amts(Amt, AmtReason)
    ORDER BY Amt DESC
    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Scott,  I hope you dont mind me bringing up an older topic, but I have question about this or rather I am trying to add to this logic and am having trouble doing so.

    First off this works/worked great, but the requirements have changed a little.  If Amt1 and Amt2 have the same value, I need to find a way to always take the Amt1Reason.  I've tested this new requirement and it seems to always take the Amt2Reason if they're the same.

    Any idea on the best way to go about this?

     

    As always, any and all help is much appreciated.

  • I would add a priority field and add it to the order  by.

    So, using Scott's code, it would now look like this ...

    SELECT d.ID, ca1.Amt, ca1.AmtReason
    FROM #data d
    CROSS APPLY (
    SELECT TOP (1) Amt, AmtReason
    FROM ( VALUES(d.amt1, d.amt1Reason, 1), (d.amt2, d.amt2Reason, 2), (d.amt3, d.amt3Reason, 3) /*,...*/ ) AS amts(Amt, AmtReason, AmtPriority)
    ORDER BY Amt DESC, AmtPriority
    ) AS ca1
  • DesNorton,

     

    Thank you so much, this looks to be exactly what I need.  Going to test some more.

    Thank again

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply