Reply to Max(date) using joins

Your name:

Reply:


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.

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация