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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compare Values from Same Table

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 NewAssessmentDate
FROM
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
) y2
WHERE
y1.Score > y2.PreviousScore;
Go to Top of Page

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 AssessmentDate
6052 804 2011-08-24
6052 817 2011-09-26
6052 833 2012-01-10

In 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 :)
Go to Top of Page

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 NewAssessmentDate
FROM
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;
Go to Top of Page

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 :)
Go to Top of Page
   

- Advertisement -