Solved

Need help with a query

Posted on 2012-03-23
6
311 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Scheduling Jobs for Execution: 4 16
SQL Server creating a temp table 7 40
Service Statictic 11 14
SqlAdvisor 2016 3 11
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now