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!
LVL 6
JeffG2583Asked:
Who is Participating?
 
plummetConnect With a Mentor Commented:
Whoops, my example had a small error, this should fix that:

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 	enr.rnk =1
and 	ActionClass='W'

Open in new window

0
 
CrashmanConnect With a Mentor Commented:
SELECT MAX(StartDate) as StartDate, ActionClass, StudentID  FROM Enrollment enr WHERE ActionClass='W' 
Group by ActionClass,StudentID

Open in new window


Select StudentID, ActionClass, StartDate from (
Select RANK() OVER (Partition BY StudentID Order BY StartDateDesc) as Rnk, StudentID, ActionClass, StartDate from Enrollment) D where rnk =1

Open in new window

0
 
plummetCommented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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

0
 
Scott PletcherSenior DBACommented:
>> 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?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
@JeffG2583

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

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.