SQL Scripting – Identify the Most Recent Score

Variable Manager – SQL Script tip: identify the most recent score

To use a SQL scripted score to identify the most recent in a series of scores… for example, the most recent game in a season…  you first need to set up a sequence of scores, each referencing a survey or observation relating to a game. A 10-game season with a score for Footwork might look something like this…

TECH_FOOT_00_G01
TECH_FOOT_00_G02
TECH_FOOT_00_G03
TECH_FOOT_00_G04
TECH_FOOT_00_G05
TECH_FOOT_00_G06
TECH_FOOT_00_G07
TECH_FOOT_00_G08
TECH_FOOT_00_G09
TECH_FOOT_00_G10

By using the SQL CASE statement, you can step through the scores one at a time to determine which one contains data.

CASE
WHEN [TECH_FOOT_00_G10] is not null then ([TECH_FOOT_00_G10])
WHEN [TECH_FOOT_00_G09] is not null then ([TECH_FOOT_00_G09])
WHEN [TECH_FOOT_00_G08] is not null then ([TECH_FOOT_00_G08])
WHEN [TECH_FOOT_00_G07] is not null then ([TECH_FOOT_00_G07])
WHEN [TECH_FOOT_00_G06] is not null then ([TECH_FOOT_00_G06])
WHEN [TECH_FOOT_00_G05] is not null then ([TECH_FOOT_00_G05])
WHEN [TECH_FOOT_00_G04] is not null then ([TECH_FOOT_00_G04])
WHEN [TECH_FOOT_00_G03] is not null then ([TECH_FOOT_00_G03])
WHEN [TECH_FOOT_00_G02] is not null then ([TECH_FOOT_00_G02])
WHEN [TECH_FOOT_00_G01] is not null then ([TECH_FOOT_00_G01])
ELSE NULL
END

You can also use the CASE statement to get an average of the previous 3 game scores.

CASE 
WHEN [TECH_FOOT_00_G10] is not null then (([TECH_FOOT_00_G09] + [TECH_FOOT_00_G08] + [TECH_FOOT_00_G07]) / 3)
WHEN [TECH_FOOT_00_G09] is not null then (([TECH_FOOT_00_G08] + [TECH_FOOT_00_G07] + [TECH_FOOT_00_G06]) / 3)
WHEN [TECH_FOOT_00_G08] is not null then (([TECH_FOOT_00_G07] + [TECH_FOOT_00_G06] + [TECH_FOOT_00_G05]) / 3)
WHEN [TECH_FOOT_00_G07] is not null then (([TECH_FOOT_00_G06] + [TECH_FOOT_00_G05] + [TECH_FOOT_00_G04]) / 3)
WHEN [TECH_FOOT_00_G06] is not null then (([TECH_FOOT_00_G05] + [TECH_FOOT_00_G04] + [TECH_FOOT_00_G03]) / 3)
WHEN [TECH_FOOT_00_G05] is not null then (([TECH_FOOT_00_G04] + [TECH_FOOT_00_G03] + [TECH_FOOT_00_G02]) / 3)
ELSE NULL
END

= = = = =

Version(s): 18.1 and later