I had a client recently that needed me to get the direction a student’s GPA was headed on a regular basis.
To do this I needed to get the last GPA record on the table and the previous GPA. The dates on these rows were different for different schools and each year the dates could change, so the query had to be dynamic. I could not hard code in the dates. I also wanted to avoid a cursor due to performance issues.
This was very easy with Common Table Expressions (CTE’s). Below is the query. The first CTE (CurrentDate) gets a list of the GPAs and adds a row number to the results. The second CTE (PrevDate) is the same query but I added a one to the row number. The next CTE (LastDate) Gets the last date the GPA was recorded.
Now with all of those CTE’s complete you can combine them with the finally query to get the comparison of the GPAs. You do an inner join with last date to eliminate all of the older dates. Then join the current date and previous date CTEs, since the row numbers are off by one number the previous GPA and the current GPA are on the same row now. Then it is a simple subtraction to determine if the GPA is rising or falling.
With
CurrentDate as(
Select f.DimDWPersonKey, f.SchoolDateKey,isnull(f.CurGPA,0) CurrentGPA, ROW_NUMBER() Over(Partition by dimdwpersonkey order by dimdwpersonkey,schooldatekey ) RowNum
From FactSchoolPerson f
),
PrevDate as (
Select f.DimDWPersonKey, f.SchoolDateKey,isnull(f.CurGPA,0) PrevGPA,(ROW_NUMBER() Over(Partition by dimdwpersonkey order by dimdwpersonkey,schooldatekey )+1) RowNum
From FactSchoolPerson f
),
LastDate as(
Select f.DimDWPersonKey, Max(f.SchoolDateKey) LastDate
From FactSchoolPerson f
Group by f.DimDWPersonKey
)
Select cd.DimDWPersonKey, cd.CurrentGPA – pd.PrevGPA GPADirection
From CurrentDate cd Left Join
PrevDate pd on cd.DimDWPersonKey = pd.DimDWPersonKey and cd.RowNum = pd.RowNum Inner Join
LastDate ld on ld.DimDWPersonKey = cd.DimDWPersonKey and ld.LastDate = cd.SchoolDateKey
Where pd.SchoolDateKey is not null
Order by cd.DimDWPersonKey
