Need help with a query
Posted on 2012-03-23
OK, I'm not smart enough to figure out how this query should be written so I'm turning to the experts. :)
The tables: There are two tables, Student and Enrollment.
Student contains 2 fields called StudentNumber and StudentID
Enrollment contains 3 fields: StartDate, ActionClass and StudentID
This query will return the info I need if I query for a specific studentid...
SELECT top 1 StartDate, ActionClass FROM Enrollment enr WHERE ActionClass='W' order by ReportedTime desc
What I need: I need a list of StudentNumber and StartDate where the above query returns 'W'. Students will have multiple Enrollment records and the above query is the only way to get the most recent record and check if it's W for withdrawn.
Please let me know if you have questions. Thanks in advance!