Help avoiding long IF statements

Question

Post reply

Help avoiding long IF statements

Mark Eckeard

Hall of Fame

Points: 3462
More actions
October 11, 2019 at 3:35 pm
Go to Answer
#3688080

All,
I have 3 variables (called Medals) that hold 1 of 5 values. I have a 4th variable (called overall medal) that is set based on the values of the 3 previously mentioned vars.
Here are my 3 vars for the individual medals:
@PP_Medal
@SU_Medal
@QAS_Medal
The possible values for these are, from worst to best:
Null
Performance Standard
Bronze
Silver
Gold
And here is the overall medal var:
@Overall_Medal
The first 3 vars have their values set based on some other stats and that part is fine. I'm hard coding different values for testing purposes but this code will become a function and pass in the 3 values and pass back the value for Overall_Medal.
On to the logic part and my issue...
If all 3 values for the 3 vars match, the Overall_Medal will match. For example, this:
@PP_Medal = 'Gold'
@SU_Medal = 'Gold'
@QAS_Medal = 'Gold'
Would result in the following:
@Overall_Medal = 'Gold'
The same applies to any of the 5 possible values passed in. If all 3 match, Overall_Medal will get that value. This part is fine.
My next rule is that if any of the 3 vars are Null, the Overall_Medal automatically is set to Null. I'm fine with the logic for this too. It's the next rule I need help with. If all 3 categories have a value that do not match, the Overall_Medal is set to the lowest/worst of the 3. For example, this:
PP_Medal = 'Gold'
SU_Medal = 'Bronze'
QAS_Medal = 'Gold'
Would result in the following:
Overall_Medal = 'Bronze'
Basically the Overall_Medal downgrades to the lowest/worst value of the 3 vars.
This can be achieved with If statements but I don't like how much code I would need to check for all possible combinations, like
this:
if ((@SU_Medal = 'Silver' and @PP_Medal = 'Gold' and @QAS_Medal = 'Gold' )
or (@SU_Medal = 'Gold' and @PP_Medal = 'Silver' and @QAS_Medal = 'Gold' )
or (@SU_Medal = 'Gold' and @PP_Medal = 'Gold' and @QAS_Medal = 'Silver' )
or (@SU_Medal = 'Silver' and @PP_Medal = 'Silver' and @QAS_Medal = 'Gold' )
or (@SU_Medal = 'Silver' and @PP_Medal = 'Gold' and @QAS_Medal = 'Silver' )
or (@SU_Medal = 'Gold' and @PP_Medal = 'Silver' and @QAS_Medal = 'Silver' ))
begin
set @Overall_Medal = 'Silver';
end
This works but seems overkill. Is there cleaner way to set my Overall_Medal var to the lowest/worst value in the cases where the 3 vars do not have the same value?
Thanks,
Mark

DesNorton

SSC-Insane

Points: 24401

October 11, 2019 at 5:46 pm

Answer

#3688107

I know that the OP stated that he was OK with NULL and All the same medals.

However, @luis soultion does not take NULL into account.

This should do the trick

DECLARE @PP_Medal      varchar(25) = NULL;
DECLARE @SU_Medal      varchar(25) = 'Silver';
DECLARE @QAS_Medal     varchar(25) = 'Performance Standard';

DECLARE @Overall_Medal varchar(25);

SELECT TOP(1) @Overall_Medal = NULLIF(POS.MedalDescription, '')
FROM ( VALUES (ISNULL(@PP_Medal, '')), (ISNULL(@SU_Medal, '')), (ISNULL(@QAS_Medal, '')) ) AS Medal(MedalDescription)
INNER JOIN (
  VALUES ( 1, '' )
       , ( 2, 'Performance Standard' )
       , ( 3, 'Bronze' )
       , ( 4, 'Silver' )
       , ( 5, 'Gold' )
            ) AS POS(MedalRank, MedalDescription)
ON Medal.MedalDescription = POS.MedalDescription
ORDER BY POS.MedalRank;

SELECT Overall_Medal = @Overall_Medal;

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Something like this?

CREATE TABLE #MedalMetals (Metal varchar(25), MedalRank int);
INSERT INTO #MedalMetals
VALUES
('Performance Standard', 1),
('Bronze', 2),
('Silver', 3),
('Gold', 4);

DECLARE @PP_Medal varchar(25) = 'Bronze';
DECLARE @SU_Medal varchar(25) = 'Silver';
DECLARE @QAS_Medal varchar(25) = 'Gold';
DECLARE @Overall_Medal varchar(25);
DECLARE @Medals table (Medal varchar(15), Metal varchar(25));
INSERT INTO @Medals 
VALUES
('PP_Medal', @PP_Medal),
('SU_Medal', @SU_Medal),
('QAS_Medal', @QAS_Medal);

SET @Overall_Medal = (
SELECT TOP 1 mm.Metal
FROM #MedalMetals mm
JOIN @Medals m ON mm.Metal = m.Metal
ORDER BY MedalRank
);

SELECT @Overall_Medal;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

With just a few values, I believe that temp tables or table variables are not really needed.

DECLARE @PP_Medal varchar(25) = 'Bronze';
DECLARE @SU_Medal varchar(25) = 'Silver';
DECLARE @QAS_Medal varchar(25) = 'Gold';
DECLARE @Overall_Medal varchar(25);

SELECT TOP 1 
    Overall_Medal = mm.Metal
FROM (VALUES('Performance Standard', 1),
            ('Bronze', 2),
            ('Silver', 3),
            ('Gold', 4)) mm(metal, MedalRank)
JOIN (VALUES(@PP_Medal),
            (@SU_Medal),
            (@QAS_Medal)) m(metal) ON mm.Metal = m.Metal
ORDER BY MedalRank;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 3

Mark Eckeard

Hall of Fame

Points: 3462

October 11, 2019 at 5:59 pm

#3688108

John,

That is perfect. Thanks!

Mark

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Mark

You're welcome. You should consider Luis's or Des's variations as well - I think they're right not to fiddle about with temp tables. My solution was cobbled together in five minutes, so I just wrote down the first thing that came into my head.

John