How To Use IF Statements In Your SET BASED SQL
By Strictly-Software
Now if you are new to SQL you may not know the benefits of the CASE statement. It is basically the way you can implement an IF statement within your SET Based SQL.
I do a lot of work with Horse Racing and I like to rank my horses on a number of variables adding up a "Rating" score by appending values to a default value so that runners with the best score appear at the top.
An example of this would be the simplified cut down version below where I am adding points to a Ratings column that will be displayed in the final results. I used multiple CASE WHEN THEN ELSE END, statements to check various stats a horse may have and if they meet the criteria I append a score to a rolling count e.g
This example uses a couple of CASE statements to show me the horses Last Races Official Rating, but also creates a SCORE using a number of factors which I can then order by easily at the end.
The SQL
SELECT RacePK,RunnerPK,Racedatetime,IsHandicap,CourseName + N' - ' + RaceTime + ' - ' + HorseName + ' @ ' + CurrentOdds as Info,OfficialRating,
CASE WHEN LastRaceFK>0 THEN (SELECT OfficialRating FROM RACE_RUNNERS WITH (NOLOCK) WHERE RaceFK=run.LastRaceFK AND HorseNameCountry=run.HorseNameCountry) ELSE 0 END as LastRaceOfficialRating,Class
CASE ClassChange WHEN 1 THEN 'UP IN CLASS' WHEN 2 THEN 'DOWN IN CLASS' WHEN 0 THEN 'SAME CLASS' ELSE 'NA' END as ClassChange,
CASE WHEN LastRaceFK = 0 THEN 'NA'
WHEN LastRaceFK > 0 AND (SELECT Class FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST CLASS INFO'
WHEN LastRaceFK > 0 THEN 'LAST RACE CLASS: ' + (SELECT MyClass + ' - ' + RaceName FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) ELSE 'NA' END as LastRaceClass, Going, GoingCode,
CASE WHEN LastRaceFK = 0 THEN 'NA'
WHEN LastRaceFK > 0 THEN 'LAST RACE GOING: ' + (SELECT UPPER(GOINGCODE) FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK)
WHEN LastRaceFK > 0 AND (SELECT GOINGCODE FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST RACE GOING INFO' END as LastRaceGoing,
LastFinishPos, FORM,
Score = CASE WHEN Favourite = 1 THEN 50
WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,2) THEN 35
WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,3) THEN 20
WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,4) THEN 10
ELSE 0 END +
-- 0 = no change, 1 = up in grade, 2 down in grade, 3 = unknown, -1 default value
CASE WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) AND LastFinishPos = 1 THEN 50
WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) THEN 45
WHEN ClassChange = 1 AND LastFinishPos = 1 AND (HasMaxOfficialRating = 1 OR Favourite = 1) THEN 40
WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 35
WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 30
WHEN ClassChange = 1 AND KeyForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 27
WHEN ClassChange = 1 AND KeyForm LIKE '%L%' THEN 25
-- down in grade could have won at this grade before
WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos = 1 THEN 35
WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 30
WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' THEN 27
WHEN ClassChange IN(0,1) THEN 5
WHEN ClassChange = 2 THEN 3
WHEN ClassChange IN(3,-1) THEN - 5
ELSE 0
END +
-- new IF for no of runs the more win percentage the better
CASE WHEN Runs > 5 AND WinPerc = 0 THEN -20
WHEN Runs > 5 AND WinPerc = 100 THEN 100
WHEN Runs > 2 AND WinPerc = 100 THEN 50
WHEN Runs > 5 AND PlacePerc = 100 THEN 60
WHEN Runs > 2 AND PlacePerc = 100 THEN 30
WHEN Runs > 5 AND PlacePerc > 70 THEN 50
WHEN Runs > 2 AND PlacePerc > 70 THEN 30
WHEN Runs > 2 AND LosePerc = 100 THEN -50
WHEN Runs > 5 AND LosePerc > 60 THEN -60
WHEN Runs = 0 OR LOSEPERC > 70 THEN -50 ELSE -10 END +
-- Official Rating higher the better
CASE WHEN OfficialRating > 170 THEN 300
WHEN OfficialRating > 160 THEN 275
WHEN OfficialRating > 150 THEN 250
WHEN OfficialRating > 125 THEN 200
WHEN OfficialRating > 110 THEN 175
WHEN OfficialRating > 105 THEN 150
WHEN OfficialRating > 105 AND IsHandicap = 1 THEN 100
WHEN HasMaxOfficialRating = 1 THEN 50
ELSE 10 END
FROM RACES as ra with (nolock)
JOIN RACE_RUNNERS as run with (nolock)
ON ra.RacePK = run.RaceFK
JOIN COURSES as c with (nolock)
ON c.CoursePK = r.CourseFK
WHERE RacePK = 269330 -- current race I am looking at
The Results
Below are the results from that query outputted in MS SQL Management Studio.
Another Example
This is where I am using a CASE WHEN THEN ELSE END, statement in the ORDER By clause to show the output of a race card.
The race could be finished and in that case I would want the ORDER of the results to be from no 1 (winner) to the horse that finished last. I would also want any runners not finishing who would get a finish position of 0 appearing last.
I would also want any non-runners, horses who were pulled out of the event, before the race to appear at the bottom of the results.
Also with racing, in the flat, horses start the race in stalls, so if it is a flat race I want to show the stall they are in, if the race is a jump or NHF (Bumper or National Hunt Flat Race), then they use no stalls so I would not order by stall no.
The SQL
SELECT RunnerPK,Racename,raceType,HorseName,Jockey,NonRunner,FinalOdds,CurrentOdds,Draw,Finishposition,favourite,officialrating
FROM RACE_RUNNERS as run WITH (nolock)
JOIN RACES as ra WITH (nolock)
ON ra.RacePK=run.RaceFK
WHERE RacePK=270423
ORDER BY CASE WHEN nonrunner=1 THEN 150
WHEN EXISTS(select top(1) runnerPK from race_runners as r1 WITH (nolock) where r1.Racefk=ra.RacePK and finishposition>0) THEN
CASE WHEN finishposition=0 then 100 ELSE finishposition END
ELSE CASE when ra.racetype IN('NH','B') THEN horsename else draw END
END The Results
Below are the results from that query outputted in MS SQL Management Studio.
As I hope you can see the use of a CASE statement either in a SELECT clause or an ORDER BY is very useful for adding an IF statement into a SET based query.
You can also use them in your JOINS to help decide which table you actually JOIN onto, for example if I had two tables, one for RACES that had not been run yet, and one for RACES that had finished I could add a CASE statement into the JOIN so that if there existed a runner with a finish position or a RACE with a finishing time I would join to the RACE_RESULTS table and for RACES not run yet I would join to the RACE_CARD table (if they were named like that for races before running and after a result was in).
There are actually lots of ways you can use CASE statements in your SQL and it may help you to be able to CONVERT looping code or procedural code with actual IF statements in a ROW by ROW CURSOR or LOOP into a SET BASED SQL statement that will perform a lot quicker.
Please post your favourite CASE statement usages in the comments section.
By Strictly-Software
Posts
No comments:
Post a Comment