Compare columns within the same row

Question

Compare columns within the same row

GBeezy

SSCarpal Tunnel

Points: 4723
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, 10 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: 101087 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

GBeezy SSCarpal Tunnel Points: 4723 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: 24381 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: 4723 More actions · Answer 4

DesNorton,

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

Thank again