Comparing Previous Rows in SQL

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.


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

This entry was posted in Syndication, T-SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s