asked on

Need help with a query

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!

SOLUTION

Haver Ramirez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plummet

Hi Jeff,

Building on the work Crashman has done:

Select 
	s.StudentNumber,
	enr.StartDate
from (	Select 
	StudentID, 
	ActionClass, 
	StartDate,
	RANK() OVER (Partition BY StudentID Order BY StartDate Desc) as rnk
	from Enrollment) enr
inner join Student s 
  on s.StudentID = enr.StudentID
where 	d.rnk =1
and 	ActionClass='W'

Open in new window

This should return the information you want, I think.

Hope it works as expected!

John

Racim BOUDJAKDJI

This should give you a list of all students and dates, who have an ActionClass of W on their most recent occurrence on the table. If you need the student who have not withdrawn yet simply switch the where clause value to any other code.

Hope this helps...

select distinct A.StudentID, A.StartDate
from Enrollment A
         inner join (select StudentID, max(StartDate) max_date from Enrollment group by StudentID) B
         on A.StudentID=B.StudentID and A.StartDate =B.max_date
where A.ActionClass = 'W'
order by A.StudentID

Open in new window

ASKER CERTIFIED SOLUTION

plummet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

>> 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. <<

Your original code doesn't quite do that. It finds the most recent Withdrawn, regardless of whether it's the last action or not.

>> SELECT top 1 StartDate, ActionClass FROM Enrollment enr WHERE ActionClass='W' order by ReportedTime desc <<

So Enrollment has at least four relevant columns, not three, because it includes ReportedTime? -- Yes?

Racim BOUDJAKDJI

@JeffG2583

Can you provide feedback on the code I supplied you with. Let us know if we can help you further.

Thanks