Solved

Need help with a query

Posted on 2012-03-23
6
313 Views
Last Modified: 2012-03-29
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
Comment
Question by:JeffG2583
6 Comments
 
LVL 8

Assisted Solution

by:Crashman
Crashman earned 100 total points
ID: 37759522
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
 
LVL 10

Expert Comment

by:plummet
ID: 37760202
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37760367
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Accepted Solution

by:
plummet earned 400 total points
ID: 37760368
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37766696
>> 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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37767668
@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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question