Posted by Plamen Ratchev on 09/30/53 12:00

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

• Next in forum: Re: A philosophical question about inserts
• Prev in forum: Re: Max(date) using joins
• Thread view: Re: Max(date) using joins

[Reply to this message]