Please help! I am at my wit's end with this one.

I have two tables, SAFs (which contains course information) and
SAF_Status (which contains records of when and how a course in the SAF
table is modified). Many courses have multiple entries in the Status
table. I am trying to create a query that will show all of the
courses in SAF but only with the most recent status (i.e.,
Max(ActionDate)).


The joined tables contains something like this:

CourseID Subj. Number. ActionDate
123 BIOL 101 1/8/2008
123 BIOL 101 12/2/2007
120 BIOL 123 5/5/2007
231 BIOL 321 2/6/2007
120 BIOL 123 6/23/2007

What I want is:

CourseID Subj. Number. ActionDate
123 BIOL 101 1/8/2008
120 BIOL 123 6/23/2007
231 BIOL 321 2/6/2007



The statement below only gives me the Max record for the entire table
rather than the max record for each course.

SELECT dbo.SAF2_Status.ActionDate, dbo.SAF2_Status.Status,
dbo.SAFs.Term, dbo.SAFs.Subj, dbo.SAFs.SubjOther, dbo.SAFs.Crse,
dbo.SAFs.Course_Title , dbo.SAFs.CH, dbo.SAFs.Dept, dbo.SAFs.Mode,
dbo.SAFs.Cap, dbo.SAFs.Site, dbo.SAFs.CESAFID, dbo.SAFs.SiteOther,
dbo.SAFs.Inst1, dbo.SAFs.Inst2, dbo.SAFs.NewInstructor
FROM dbo.SAF2_Status, dbo.SAFs
WHERE ((dbo.SAFs.CESAFID = dbo.SAF2_Status.CESAFID) AND
( (dbo.SAFs.Term = Tvar) AND (dbo.SAFs.Dept = 'Dvar')) AND
((dbo.SAF2_Status.Status = 'DEPTSETUP') OR (dbo.SAF2_Status.Status =
'DEPTQUEUE') OR (dbo.SAF2_Status.Status = 'CERTRN') OR
(dbo.SAF2_Status.Status = 'DEPTCORRREQ'))) AND
(dbo.SAF2_Status.ActionDate = (SELECT MAX( dbo.SAF2_Status.ActionDate)
FROM dbo.SAF2_Status))



How can I do this using an SQL statement?

Any assistance would be greatly appreciated.

t8ntboy (t8ntboy (AT) gmail (DOT) com) writes:
WITH numbered (CourseID, Subj, Number, ActionDate, rowno) AS
SELECT CourseID, Subj, Number, ActionDate,
row_number() OVER (PARTITION BY CourseID ORDER BY ActionDate DESC)
FROM tbl
)
SELECT CourseID, Subj, Number, ActionDate
FROM numbered
WHERE rowno = 1

This solution requires SQL 2005. (Please next time specify which version
of SQL Server you are using.)

On SQL 2000 you can do:

SELECT a.CourseID, a.Subj, a.Number, a.ActionDate
FROM tbl a
JOIN (SELECT CourseID, ActionDate = MAX(ActionDate)
FROM tbl
GRUOP BY CourseID) AS b ON a.CourseID = b.CourseID
AND a.ActionDate = b.ActionDate


I leave it as an exercise to fit this into your current query.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

You need to add correlation to the course in the subquery to get the MAX
ActionDate for the most recent status. Here is a modified query that should
get you the data (I also simplified your WHERE conditions, hope got it right
after removing all those parentheses):

SELECT S.ActionDate,
S.Status,
C.Term,
C.Subj,
C.SubjOther,
C.Crse,
C.Course_Title ,
C.CH,
C.Dept,
C.Mode,
C.Cap,
C.Site,
C.CESAFID,
C.SiteOther,
C.Inst1,
C.Inst2,
C.NewInstructor
FROM dbo.SAF2_Status AS S
INNER JOIN dbo.SAFs AS C
ON S.CESAFID = C.CESAFID
WHERE C.Term = 'Tvar'
AND C.Dept = 'Dvar'
AND S.Status IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN', 'DEPTCORRREQ')
AND S.ActionDate = (
SELECT MAX( S1.ActionDate)
FROM dbo.SAF2_Status AS S1
WHERE S1.CESAFID = C.CESAFID
AND S1.Status IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN',
'DEPTCORRREQ'))

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jan 15, 6:00*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:


Thanks, that worked beautifully!

A follow-up question: I have another table that contains instructor
names and IDs. How would I join that table to C.Inst1? I think it
would need to be a Left Outer Join since there can be courses with no
assigned instructors.

Yes, it would have to be a LEFT OUTER JOIN, something like this:

....
FROM dbo.SAF2_Status AS S
INNER JOIN dbo.SAFs AS C
ON S.CESAFID = C.CESAFID
LEFT OUTER JOIN dbo.Instructors AS I
ON C.Inst1 = I.Inst1
....

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jan 16, 12:23*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:


Once again, works great! Thanks!

Is it possible to exclude courses if there most recent status is not
('DEPTSETUP', 'DEPTQUEUE', 'CERTRN',
'DEPTCORRREQ')?

For instance,

A course record may look like this:

Course Status Date
BIOL 101 DEPTSETUP 5/5/2007
BIOL 101 CERTRN 6/30/2007
BIOL 101 REMOVE 7/8/2007


Since the status REMOVE is the most recent I want the course to be
excluded from the results.

The SQL statement above overlooks REMOVE since it is not part of the
IN clause even though it is the most recent status.

One way to do this is to change the subquery that checks for the latest
status, like this:

....
AND S.ActionDate = (
SELECT MAX(S1.ActionDate)
FROM dbo.SAF2_Status AS S1
WHERE S1.CESAFID = C.CESAFID
AND S1.Status IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN',
'DEPTCORRREQ')
HAVING MAX(S1.ActionDate) = (SELECT MAX(S2.ActionDate)
FROM dbo.SAF2_Status AS
S2
WHERE S2.CESAFID =
C.CESAFID))

Note the added HAVING clause.

An alternative is to change the main query FROM and WHERE, like below:

.... your select <list> goes here
FROM (SELECT CESAFID,
[Status],
MAX(ActionDate) AS ActionDate
FROM dbo.SAF2_Status AS S1
WHERE [Status] IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN', 'DEPTCORRREQ')
GROUP BY CESAFID, [Status]
HAVING MAX(ActionDate) = (SELECT MAX(S2.ActionDate)
FROM dbo.SAF2_Status AS S2
WHERE S1.CESAFID = S2.CESAFID))
AS S
INNER JOIN dbo.SAFs AS C
ON S.CESAFID = C.CESAFID
LEFT OUTER JOIN dbo.Instructors AS I
ON C.Inst1 = I.Inst1
WHERE C.Term = 'Tvar'
AND C.Dept = 'Dvar'


HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jan 16, 1:43*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Any reason the following would not accomplish the same thing? It
seems to work.


SELECT C.CESAFID, C.Term, C.Subj, C.SubjOther, C.Crse, C.Course_Title,
C.Inst1, C.Inst2, C.NewInstructor, C.CH, C.Dept, C.Mode, C.Cap,
C.Site, C.SiteOther, S.ActionDate, S.Status, S.CESAFID, F.EID,
F.FirstName, F.LastName FROM dbo.SAFs AS C INNER JOIN dbo.SAF2_Status
AS S ON S.CESAFID = C.CESAFID LEFT OUTER JOIN dbo.Faculty AS F ON
F.EID = C.Inst1 WHERE C.Term=Tvar AND C.Dept='Dvar' AND S.Status IN
('DEPTSETUP', 'DEPTQUEUE', 'CERTRN','DEPTCORRREQ') AND S.ActionDate =
(SELECT MAX (S1.ActionDate)
FROM dbo.SAF2_Status AS S1
WHERE S1.CESAFID=C.CESAFID AND S1.Status NOT IN ('DEPTRMVLST'))
ORDER BY C.Crse

I think your query returns the same results only by coincidence. If you have
only the following rows in table SAF2_Status:

Course | Status | Action Date
--------- ---------------- ----------------
1111 DEPTQUEUE Jan-1-2008
1111 DEPTRMVLST Jan-5-2008

Then your query will return course 1111 because 'Jan-1-2008' is the latest
date according to your subquery conditions and it satisfies the WHERE
conditions. And this is incorrect according to your requirements.

Your query may return the correct results if you have another row like below
(and you probably have such rows, which is the reason you see the results
matching):

Course | Status | Action Date
--------- ---------------- ----------------
1111 DEPTABCDE Jan-3-2008

Note, it is another code not in the list ('DEPTSETUP', 'DEPTQUEUE',
'CERTRN', 'DEPTCORRREQ') which now has the latest Action Date because code
'DEPTRMVLST' is excluded.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

t8ntboy wrote:

If you have something like this that's used a lot, you may want to
wrap it in a view, e.g.

create view CourseCurrentStatus as
select Course, Status
from Courses c1
where ActionDate = (
select max(ActionDate)
from Courses c2
where c2.Course = c1.Course
)

and then JOIN to that view (same as you would to a table).