Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tomrippity
Starting Member
37 Posts |
Posted - 2012-01-31 : 09:35:34
|
I deal with test scores in a large table. Each year students are tested multiple times and I need to figure out which students scores have gone up.Lets assume that there are only 3 columns in the table (StudentId, Score, AssessmentDate). I need the score and the assessment date to be greater than the previous record for them to show up.I created a rather complex query to figure this out, but it times out with large sets of data... I'm thinking there must be a very simple way to do this, but I'm not great with SQL queries, so I'm looking here for help.(Edit: I need to compare the oldest record to the newest record and ignore all of those in between.)Thank you in advance. |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-31 : 10:04:34
|
I had posted a reply a few minutes earlier, which was completely bogus - must have been still too early in the morning for me. A better attempt here: SELECT y1.StudentId, y2.PreviousScore, y2.PreviousAssessmentDate, y1.Score AS NewScore, y1.AssessmentDate AS NewAssessmentDateFROM YourTable y1 CROSS APPLY ( SELECT TOP (1) y2.Score AS PreviousScore, y2.AssessmentDate AS PreviousAssessmentDate FROM YourTable y2 WHERE y2.StudentId = y1.StudentId AND y2.AssessmentDate < y1.AssessmentDate ORDER BY y2.AssessmentDate DESC ) y2WHERE y1.Score > y2.PreviousScore; |
 |
|
tomrippity
Starting Member
37 Posts |
Posted - 2012-01-31 : 10:08:15
|
This works great if there are only 2 records for each student! What would I need to do to only return one set of values if there are 3 sets though.Example data:Stuid Score AssessmentDate6052 804 2011-08-246052 817 2011-09-266052 833 2012-01-10In the example you gave I would be returned two records, comparing the dates in steps... I really only want to compare the very first and very last record.Originally I should have said I need to compare the oldest record to the newest record and ignore all of those in between.Thank you so much for your help. I can get what I want from the data returned, I just need to do an additional step :) |
 |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-31 : 11:42:51
|
Ah, I thought you wanted to see every time when someone improved their score. If you want to see whether the score improved only from the first time they took the assessment to the latest time they took it try the following query. Here, I am also breaking it up by year. If you don't want that, remove all the "Year(AssessementDate)"s from the query:;WITH cte AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY StudentId, YEAR(AssessmentDate) ORDER BY AssessmentDate ASC) AS RNA, ROW_NUMBER() OVER (PARTITION BY StudentId, YEAR(AssessmentDate) ORDER BY AssessmentDate DESC) AS RND FROM YourTable)SELECT y1.StudentId, y2.Score PreviousScore, y2.AssessmentDate PreviousAssessmentDate, y1.Score AS NewScore, y1.AssessmentDate AS NewAssessmentDateFROM cte y1 INNER JOIN cte y2 ON y1.StudentId = y2.StudentId AND YEAR(y1.AssessmentDate) = YEAR(y2.AssessmentDate)WHERE y1.RND = 1 AND y2.RNA = 1 AND y1.Score > y2.Score; |
 |
|
tomrippity
Starting Member
37 Posts |
Posted - 2012-01-31 : 13:12:09
|
Thank you for the help. This works perfectly.As expected, my original version was way to inefficient :) |
 |
|
|
|
|
|
|