Hi guys,
I need some help with a query that will return the consecutive number of times a student appears in a database.
Brief background:
Every week I upload a list of students into a database (anywhere from 1-300) per upload. For every student that appears in the most recent upload, I’d like to see if they also appear in the previous week’s upload and if they do, also see if they appear in the week prior to that and so on and so on.
Every student has an id, and a student name, and is categorized by an upload number representing the week they were uploaded (1, 2, 3, 4, etc. etc.).
Any ideas on how to do this? Please let me know if further explanation is needed.
Example:
Upload 3: (For week 3, the most recent)
Smith, Bob
Doe, Jane
Bunyan, Paul
Upload 2:
Smith, Bob
Rogers, Ann
Bunyan, Paul
Upload 1:
Smith, Bob
Doe, Jane
Bob, Joe
Results that should be returned (keep in mind that only the students from the most recent upload need to be queried):
Smith, Bob: 3 (appears in all three week’s uploads)
Doe, Jane: 1 (even though Jane appears in Week 1 as well, I’m only interested in appearances in consecutive weeks (i.e. she doesn’t appear in week 2 so I don’t need a result returned for her))
Bunyan, Paul: 2 (appears in week 3 and week 2)
Thanks for your help with this.
Edit: Also, it’s probably helpful to know that the database is queried one student at a time to retrieve other information specific to that student. So basically the query needs to use student_name, find the number of consecutive appearances, and then return that result (rather than find and return all results for all students).