Would it work to use the start and end date of the student enrollment's related calendar instead? that way anyone who participated in the 12-13 school year would be using the start and end dates of that schools calendar. then it would not be tied to the actual enrollment start date and would allow for a late enrollee to squeak into the correct grouping.
It seems like Assessment and Enrollment should be joined by a student ID. It's a little hard to tell without knowing what your data schema looks like, but I think I'm doing something similar where our Enrollment table has a row per student per day (since it's based on attendance). Then we have a testscores table as well that has student scores. I have a sub-query that finds just the most recent row (by date) for each student then do a left join between that and the test scores table to get the full test scores table with each student's most recent enrollment record listed with their scores.
Thanks, Michael for the suggestion. We know just enough SQL to be dangerous, but not enough to write our own sub-queries. Would you mind sharing a sample of your query?
Thanks, Joe! Our concern with changing the criteria to Calendar start and end date is our students can have multiple enrollment records per calendar. We were looking for something more specific in "linking" the appropriate enrollment to the test record.
Basically, the sub query will use the "ROW_NUMBER()" function (that's what it's called in MSSQL, but there should be a similar function if you're using a different underlying DB) to add a sequence to the enrollment table...
SELECT ROW_NUMBER() OVER (PARTITION BY studentID, schoolyear ORDER BY date) as Sequence, [insert your other fields here] FROM [Enrollment]
Then you take the above query and join it on StudentID with your TestScore table and add a WHERE clause that filters for "Sequence=1", which will basically leave you with the most recent enrollment record per student.