Max(date) using joins

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.

Re: Max(date) using joins

t8ntboy (t8ntboy@gmail.com) writes:
> 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

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Max(date) using joins

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

Re: Max(date) using joins

On Jan 15, 6:00=A0pm, "Plamen Ratchev" wrote:
> 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 shoul=
d
> get you the data (I also simplified your WHERE conditions, hope got it rig=
ht
> after removing all those parentheses):
>
> SELECT S.ActionDate,
> =A0 =A0 =A0 =A0 =A0 S.Status,
> =A0 =A0 =A0 =A0 =A0 C.Term,
> =A0 =A0 =A0 =A0 =A0 C.Subj,
> =A0 =A0 =A0 =A0 =A0 C.SubjOther,
> =A0 =A0 =A0 =A0 =A0 C.Crse,
> =A0 =A0 =A0 =A0 =A0 C.Course_Title ,
> =A0 =A0 =A0 =A0 =A0 C.CH,
> =A0 =A0 =A0 =A0 =A0 C.Dept,
> =A0 =A0 =A0 =A0 =A0 C.Mode,
> =A0 =A0 =A0 =A0 =A0 C.Cap,
> =A0 =A0 =A0 =A0 =A0 C.Site,
> =A0 =A0 =A0 =A0 =A0 C.CESAFID,
> =A0 =A0 =A0 =A0 =A0 C.SiteOther,
> =A0 =A0 =A0 =A0 =A0 C.Inst1,
> =A0 =A0 =A0 =A0 =A0 C.Inst2,
> =A0 =A0 =A0 =A0 =A0 C.NewInstructor
> FROM dbo.SAF2_Status AS S
> INNER JOIN dbo.SAFs AS C
> =A0 =A0ON S.CESAFID =3D C.CESAFID
> WHERE C.Term =3D 'Tvar'
> =A0 =A0AND C.Dept =3D 'Dvar'
> =A0 =A0AND S.Status IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN', 'DEPTCORRREQ')=

> =A0 AND S.ActionDate =3D (
> =A0 =A0 =A0 =A0 SELECT MAX( S1.ActionDate)
> =A0 =A0 =A0 =A0 FROM dbo.SAF2_Status AS S1
> =A0 =A0 =A0 =A0 WHERE S1.CESAFID =3D C.CESAFID
> =A0 =A0 =A0 =A0 =A0 =A0 AND S1.Status IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTR=
N',
> 'DEPTCORRREQ'))
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com



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.

Re: Max(date) using joins

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

Re: Max(date) using joins

On Jan 16, 12:23=A0pm, "Plamen Ratchev" wrote:
> 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
> =A0 =A0ON S.CESAFID =3D C.CESAFID
> LEFT OUTER JOIN dbo.Instructors AS I
> =A0 ON C.Inst1 =3D I.Inst1
> ...
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com



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.

Re: Max(date) using joins

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

Re: Max(date) using joins

On Jan 16, 1:43=A0pm, "Plamen Ratchev" wrote:
> One way to do this is to change the subquery that checks for the latest
> status, like this:
>
> ...
> =A0 AND S.ActionDate =3D (
> =A0 =A0 =A0 =A0 SELECT MAX(S1.ActionDate)
> =A0 =A0 =A0 =A0 FROM dbo.SAF2_Status AS S1
> =A0 =A0 =A0 =A0 WHERE S1.CESAFID =3D C.CESAFID
> =A0 =A0 =A0 =A0 =A0 =A0AND S1.Status IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN=
',
> 'DEPTCORRREQ')
> =A0 =A0 =A0 =A0 HAVING MAX(S1.ActionDate) =3D (SELECT MAX(S2.ActionDate)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM dbo.SAF2_Status AS
> S2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE S2.CESAFID =3D
> C.CESAFID))
>
> Note the added HAVING clause.
>
> An alternative is to change the main query FROM and WHERE, like below:
>
> ... your select goes here
> FROM (SELECT CESAFID,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 [Status],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MAX(ActionDate) AS ActionDate
> =A0 =A0 =A0 FROM dbo.SAF2_Status AS S1
> =A0 =A0 =A0 WHERE [Status] IN ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN', 'DEPTCO=
RRREQ')
> =A0 =A0 =A0 GROUP BY CESAFID, [Status]
> =A0 =A0 =A0 HAVING MAX(ActionDate) =3D (SELECT MAX(S2.ActionDate)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0FROM dbo.SAF2_Status AS S2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0WHERE S1.CESAFID =3D S2.CESAFID))
> AS S
> INNER JOIN dbo.SAFs AS C
> =A0 =A0ON S.CESAFID =3D C.CESAFID
> LEFT OUTER JOIN dbo.Instructors AS I
> =A0 ON C.Inst1 =3D I.Inst1
> WHERE C.Term =3D 'Tvar'
> =A0 =A0 AND C.Dept =3D 'Dvar'
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

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 =3D C.CESAFID LEFT OUTER JOIN dbo.Faculty AS F ON
F.EID =3D C.Inst1 WHERE C.Term=3DTvar AND C.Dept=3D'Dvar' AND S.Status IN
('DEPTSETUP', 'DEPTQUEUE', 'CERTRN','DEPTCORRREQ') AND S.ActionDate =3D
(SELECT MAX (S1.ActionDate)
FROM dbo.SAF2_Status AS S1
WHERE S1.CESAFID=3DC.CESAFID AND S1.Status NOT IN ('DEPTRMVLST'))
ORDER BY C.Crse

Re: Max(date) using joins

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

Re: Max(date) using joins

t8ntboy wrote:

> 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.

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).