only if you have a minute (tough SQL question)

[wengang]

Hi all.
I'm using SQL Server 2008. Thanks to the efforts of some great db folks on this site, I know that by using Left Join multiple times, I can build a list of records that meet conditions when compared to each other.

For example, I have a list of students in tblStudents, and a list of test scores and dates in tblScores.

I know that I can get student info from tblStudents Left Join tblScores (for test scores on date A) left join tblScores (for test scores on date B) etc. where (score on date B) > (score on date A). I've got a good query foundation for comparing scores by the same student (the left join condition) on different dates, and I can manipulate that all kinds of ways. The question is, how can a query like this when I don't know the dates, or in other words, I'm looking for any instance comparing test scores on any three (unspecified dates).

For example, With the pseudocode above, I can get the results for every student who did better on Aug. 10 than on Aug. 3 and better on Aug. 17 than on Aug. 10.

But what if I wanted a recordset of all the students who (at any point in history) had three scores in a row that got successively higher, regardless of date. In that case, the query would have to check not specified dates but any succession, and the succession of records is by date, not by ID (which does not always indicate which came first). IN other words, the query has to be able to know what the next test date was. If it is testing the conditions for the Aug. 3 test, for example, it would know that Aug. 10 is the next value to compare it against, because Aug. 10 is the next time this student took a test. I'm just giving random dates here, nothing to read into them. I just need to be able to compare records in the same table that have the same StudentID and find anywhere that three consecutive scores improved regardless of date.

If it's way complicated, just tell me, and I'll write some VB code to do this. I just thought in light of the original pseudocode being so readily available, this is probably not outside the realm of common SQL.

Thanks all.

[smendoza]

hmm sounds like fun. If I get some time later today I'll mess around with it. I'm guessing you want the test scores to be in the same year at least?

[Hobbenc]

This may not entirely solve your problem, but it'll get you closer
With help of some subquerys I was able to list the next score and date, for every persons test(without knowing any test dates). And add a new column that prints ***HIGHER*** if the newer test score was better.
The sort order must be Student, TestDate for this to work. I used Access but the transact sql should be the same.

SELECT Student, TestDate, Score, NextTestDate, 
(select Score from tblScores sc where sc.StudentID=TmpTable.ID and sc.TestDate=TmpTable.NextTestDate) AS NextScore, 
(IIF(NextScore > Score, "***HIGHER***", "")) AS Status
FROM 
(
   SELECT tblStudents.ID, tblStudents.Student, tblScores.TestDate, tblScores.Score, 
   (select Min(TestDate) from tblScores sc where sc.TestDate > tblScores.TestDate and sc.StudentID = tblStudents.ID) AS NextTestDate
   FROM tblScores INNER JOIN tblStudents ON tblScores.StudentID = tblStudents.ID
   ORDER BY tblStudents.Student, tblScores.TestDate, tblScores.Score
)  AS TmpTable
ORDER BY tblStudents.Student, tblScores.TestDate, tblScores.Score

[wengang]

just a thought. if everybody is always tested on the same days, doesn't that simplify this? In other words, the previous and next test dates are always the same for everybody. Would it be easier somehow if I added a table named tblTestDates that is just a list of testdates maybe with previous date and next date as fields. I'm not sure where to go from there, but it seems easier than the idea of figuring out previous and next dates

[Hobbenc]

Well, maybe, in that case I think the easiest way to solve it is to create that tblTestDates, but with 4 date columns. You need 4 scores to be able to spot 3 increases, right? So when you add a new record with a new TestDate, you also need to update the 3 previous rows and add that date in separate columns for every record, see picture: After that, use this query to show only students that have increased the score 3 times in a row:

SELECT *
FROM
(
  SELECT tblStudents.Student, tblScores.Score
  , (select sc.Score from tblScores sc where sc.StudentID=tblStudents.ID and sc.TestDate=tblTestDates.TestDate2) AS Score2
  , (select sc.Score from tblScores sc where sc.StudentID=tblStudents.ID and sc.TestDate=tblTestDates.TestDate3) AS Score3
  , (select sc.Score from tblScores sc where sc.StudentID=tblStudents.ID and sc.TestDate=tblTestDates.TestDate4) AS Score4
  , tblScores.TestDate, tblTestDates.TestDate2, tblTestDates.TestDate3, tblTestDates.TestDate4
  FROM (tblStudents INNER JOIN tblScores ON tblStudents.ID = tblScores.StudentID) INNER JOIN tblTestDates ON tblScores.TestDate = tblTestDates.TestDate
  ORDER BY tblStudents.Student, tblScores.TestDate
)
WHERE Score2 > Score AND Score3 > Score2 AND Score4 > Score3

The subquerys will get the scores for the 3 following tests, and the WHERE clause will do the "3-better-check":
WHERE Score2 > Score AND Score3 > Score2 AND Score4 > Score3
But since this solution now assumes common TestDates for all students, I guess if a student has not taken a certain test, the 3 increasing count check for that student will be broken and started over from scratch from the next test that student takes.