SQL - P8 estimates from the right year or latest available
This is the first of a few little posts I'll be doing on SQL as it seems like a fair few of you use it in complement with power BI.
I'll be assuming a basic knowledge of SQL so familiarity with SELECT, UPDATE and DELETE statements, subqueries and inner and out joins. If you need to get up to speed with the basics of SQL there are free courses on udacity and a good syntax reference at https://www.w3schools.com/sql/
To kick us off let's consider a problem about Progress 8 estimates on a table with 3 columns, KS2, Estimate and SeasonYr : if they exist for the year of the results then I want to use the ones from the appropriate year, but I can't just join on year because my latest estimates are from 2019 but I have actual results from 2020. So I want to use the right year if it's there and the latest available if not.
The first step is to create a view with the latest estimates, which you can do by joining the table to a subquery finding the latest year
Create view LatestP8
SELECT KS2, estimate, seasonyr
FROM P8Estimates p
JOIN (SELECT MAX(SeasonYr) LatestYr
FROM P8Estimates) my
ON p.seasonyr = my.LatestYr
ISNULL is not nothing
Now we have to choose the estimates to pull through which we can do with the v useful ISNULL function which lets us pull through a value bit supply an alternative if it's null.
So we're joining two sources of estimates to a query that has the KS2 result of the student and the year in which the results were achieved.
FROM StudentResults r
LEFT OUTER JOIN P8Estimates y
ON r.KS2 = y.KS2 AND r.SeasonYr = y.SeasonYr
JOIN LatestP8 l ON r.KS2 = l.KS2
Note that the year specific query is an outer join because results may or may not be there.
LatestP8 I've listed here as an inner join which is appropriate if joining in order to update p8 estimate in a table. If you're creating a view to use in BI you'd probably want an outer join here too as not all students will have KS2 results.