Skip to main content

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

5 个回答
  1. 2013年12月12日 19:22

    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.