|
|
Posted by t8ntboy on 09/30/52 12:00
Please help! I am at my wit's end with this one.
I have two tables, SAFs (which contains course information) and
SAF_Status (which contains records of when and how a course in the SAF
table is modified). Many courses have multiple entries in the Status
table. I am trying to create a query that will show all of the
courses in SAF but only with the most recent status (i.e.,
Max(ActionDate)).
The joined tables contains something like this:
CourseID Subj. Number. ActionDate
123 BIOL 101 1/8/2008
123 BIOL 101 12/2/2007
120 BIOL 123 5/5/2007
231 BIOL 321 2/6/2007
120 BIOL 123 6/23/2007
What I want is:
CourseID Subj. Number. ActionDate
123 BIOL 101 1/8/2008
120 BIOL 123 6/23/2007
231 BIOL 321 2/6/2007
The statement below only gives me the Max record for the entire table
rather than the max record for each course.
SELECT dbo.SAF2_Status.ActionDate, dbo.SAF2_Status.Status,
dbo.SAFs.Term, dbo.SAFs.Subj, dbo.SAFs.SubjOther, dbo.SAFs.Crse,
dbo.SAFs.Course_Title , dbo.SAFs.CH, dbo.SAFs.Dept, dbo.SAFs.Mode,
dbo.SAFs.Cap, dbo.SAFs.Site, dbo.SAFs.CESAFID, dbo.SAFs.SiteOther,
dbo.SAFs.Inst1, dbo.SAFs.Inst2, dbo.SAFs.NewInstructor
FROM dbo.SAF2_Status, dbo.SAFs
WHERE ((dbo.SAFs.CESAFID = dbo.SAF2_Status.CESAFID) AND
( (dbo.SAFs.Term = Tvar) AND (dbo.SAFs.Dept = 'Dvar')) AND
((dbo.SAF2_Status.Status = 'DEPTSETUP') OR (dbo.SAF2_Status.Status =
'DEPTQUEUE') OR (dbo.SAF2_Status.Status = 'CERTRN') OR
(dbo.SAF2_Status.Status = 'DEPTCORRREQ'))) AND
(dbo.SAF2_Status.ActionDate = (SELECT MAX( dbo.SAF2_Status.ActionDate)
FROM dbo.SAF2_Status))
How can I do this using an SQL statement?
Any assistance would be greatly appreciated.
Navigation:
[Reply to this message]
|