Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot, bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon

Links

XODOX
Impressum

#1: Yet another basic SQL question

Posted on 2008-04-22 23:24:30 by t8ntboy

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)))

Report this message

#2: Re: Yet another basic SQL question

Posted on 2008-04-23 04:22:18 by Plamen Ratchev

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

Report this message