|
|
Posted by t8ntboy on 09/30/41 12:00
On Jan 16, 1:43 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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 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 = 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
Navigation:
[Reply to this message]
|