Hello!
We are creating a new view displaying our state assessment data and we are in need of help with narrowing our data. We are including the enrollment table to select the student's school and grade. The issue is we need to tie a specific test record to the student's corresponding enrollment record.
So far we have created a custom SQL data connection using the following logic at the end of the connection:
where [Enrollment].[startDate] <= [TestScore].[date] and [TestScore].[date] <= [Enrollment].[EndDate] or
[Enrollment].[startDate] <= [TestScore].[date] and ([Enrollment].[EndDate]) is null
This works for the majority of our students, however, our test date is just a generic date. For example all student test records are imported with a date of May 1st. We may have a student that enrolled on May 2nd and took the test. This student would be excluded from our data connection.
Does anybody have thoughts on a better way of selecting the data?
Thank you!
Karen Payne
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.