Compare columns within the same row

Question

Compare columns within the same row

GBeezy

SSCarpal Tunnel

Points: 4717
More actions
January 20, 2022 at 5:34 pm

#3975333
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 2 years, 3 months ago by GBeezy.

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

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

GBeezy SSCarpal Tunnel Points: 4717 More actions · Answer 2

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.

DesNorton SSC-Insane Points: 24279 More actions · Answer 3

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

How to post data/code on a forum to get the best help.

GBeezy SSCarpal Tunnel Points: 4717 More actions · Answer 4

DesNorton,

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

Thank again