Select for a sliding timespan

[mbiro]

I have a simple table that three fields: StudentNumber, ExamGrade, ExamDate. I want to select a list of StudentNumbers where the student took at least 3 exams within a 5 day period (ExamDate) and had a grade(ExamGrade) of 70 or better on each exam. Make sense? The 5-day period is not predefined. It can be any 5 days in a row where three or more exams were taken and three or more of the exams had at least a grade of 70. This makes my head hurt. Any ideas?

 

[bborissov]

Is this a home work?
I ask because I already get a note from the administrators here when I answered on almost the same question


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mbiro]

No. Not homework. Real life trying to find cheaters for online exams.

 

[RyanEK]

Hi,

Try the following code. The derived table returns a column with a count for every other exam within 5+- days. It then grabs a distinct list where this count >= 3.

select distinct(StudentNumber) from
(
  select StudentNumber, 
  (select count(*) from [Table] t2 
    where t2.StudentNumber = t1.StudentNumber 
    and t2.ExamDate <> t1.ExamDate 
    and t2.ExamGrade >= 70
    and t2.ExamDate between dateadd(day,-5,t1.ExamDate) 
    and dateadd(day,5,t1.ExamDate)
  ) as iCount
  from [Table] t1
  where ExamGrade >= 70
) t3
where t3.iCount >= 3

Ryan