Monday 27 December 2021

SQL - Using CASE Statements IN SQL Statements

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

No comments: