The problem seems to be in your WHERE clause in the subquery to check for
MAX action date. In the subquery you define an alias for the table but
then
reference the outer tables in the WHERE. Here is the query with cleaned
table aliases. If you do not have duplicate action dates for status codes
it
should return the correct results with no duplicates:
SELECT S.ActionDate,
S.CESAFID,
S.Status,
A.CESAFID AS CESAFID_A,
A.Term,
A.CRN,
A.Subj,
A.Crse,
A.SubjOther,
A.Course_Title,
A.Site,
A.SiteOther,
A.Inst1,
A.Inst2,
A.NewInstructor,
A.CH,
A.Dept,
A.Cap,
A.Mode,
F.LastName AS [1Lname],
F.FirstName AS [1Fname],
P.PayTotal,
P.NumOfPays,
P.PayDates,
P.Fund,
P.Org,
P.Acct,
P.Program,
P.Dept AS PayDept
FROM dbo.SAFs AS A
INNER JOIN dbo.SAF2_Status AS S
ON A.CESAFID = S.CESAFID
INNER JOIN dbo.SAF2_Pay AS P
ON A.CESAFID = P.CESAFID
LEFT OUTER JOIN dbo.Faculty AS F
ON A.Inst1 = F.EID
WHERE S.ActionDate =
(SELECT MAX(S1.ActionDate)
FROM dbo.SAF2_Status AS S1
WHERE S1.CESAFID = S.CESAFID)
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|
