• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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!
0
JeffG2583
Asked:
JeffG2583
2 Solutions
 
CrashmanCommented:
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
plummetCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now