Prev: Another new DBMS
Next: Job ends successfully even when it's not true
From: t8ntboy on 22 Apr 2008 17:24
I have the following statement that is intended to provide the most
records with the most recent status from the SAF2 table, based on
dbo.SAF2_Status.ActionDate along with the corresponding record from
the SAF2_Pay table. However, it is yielding more than just the most
recent record....it is providing multiple statuses of records when I
only want the most recent one. This, in turn, is creating duplicates
from the SAF2_Pay table since it is joining on duplicated.

I only want to yield the most record status...it should be grouped by
CESAFID but I cannot get it to work.

Any assistance would be appreciated.




SELECT dbo.SAF2_Status.ActionDate, dbo.SAF2_Status.CESAFID,
dbo.SAF2_Status.Status, dbo.SAFs.CESAFID AS Expr1, dbo.SAFs.Term,
dbo.SAFs.CRN, dbo.SAFs.Subj, dbo.SAFs.Crse, dbo.SAFs.SubjOther,
dbo.SAFs.Course_Title, dbo.SAFs.Site, dbo.SAFs.SiteOther,
dbo.SAFs.Inst1, dbo.SAFs.Inst2, dbo.SAFs.NewInstructor, dbo.SAFs.CH,
dbo.SAFs.Dept, dbo.SAFs.Cap, dbo.SAFs.Mode, dbo.Faculty.LastName AS
[1Lname], dbo.Faculty.FirstName AS [1Fname], dbo.SAF2_Pay.PayTotal,
dbo.SAF2_Pay.NumOfPays, dbo.SAF2_Pay.PayDates, dbo.SAF2_Pay.Fund,
dbo.SAF2_Pay.Org, dbo.SAF2_Pay.Acct, dbo.SAF2_Pay.Program,
dbo.SAF2_Pay.Dept AS PayDept
FROM dbo.SAFs INNER JOIN dbo.SAF2_Status ON dbo.SAFs.CESAFID =
dbo.SAF2_Status.CESAFID INNER JOIN dbo.SAF2_Pay ON dbo.SAFs.CESAFID =
dbo.SAF2_Pay.CESAFID LEFT OUTER JOIN dbo.Faculty ON dbo.SAFs.Inst1 =
dbo.Faculty.EID
WHERE (dbo.SAF2_Status.ActionDate =
(SELECT MAX(ActionDate) AS Expr1
FROM dbo.SAF2_Status AS
SAF2_Status_1
WHERE (dbo.SAF2_Status.CESAFID =
dbo.SAFs.CESAFID)))
From: Plamen Ratchev on 22 Apr 2008 22:22
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

 | 
Pages: 1
Prev: Another new DBMS
Next: Job ends successfully even when it's not true