Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...