|
|
Posted by t8ntboy on 09/29/65 12:00
On Jan 15, 6:00 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> 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 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 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.
Navigation:
[Reply to this message]
|