Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select for a sliding timespan

Status
Not open for further replies.

mbiro

Programmer
Nov 20, 2001
304
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?
 
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.
 
No. Not homework. Real life trying to find cheaters for online exams.
 
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.

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top