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

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

• Next in forum: duplicate checking
• Prev in forum: A dimension playing off another dim
• Thread view: Re: Max(date) using joins

[Reply to this message]