Max date record for each location
Posted on 2012-09-04
I have the following code and it gives me what I need as long as I don't add the Dept field...but I need Dept. If I add department then it shows multiple locations with multiple dates instead of the Max date for each location. It does this even if the department is null. I think I need an inner select statement but I can't get anything to work. I could really use some help.
SELECT tblIncidents.Location, CASE WHEN tblIncidents.Location = 'Corporate' THEN Dept ELSE '' END AS Deptmt, MAX(tblIncidents.Date) AS MaxDate
FROM tblIncidents INNER JOIN
tblLocation ON tblIncidents.Location = tblLocation.Location
WHERE (tblLocation.Active = 'True') AND (tblIncidents.Date <= @Date)
GROUP BY tblIncidents.Location, tblIncidents.Dept
ORDER BY tblIncidents.Location
Sample results the code above gives me...
Location Deptmt MaxDate
Burland 10/16/2010 00:00:00
Burand 09/15/2009 00:00:00
Burland 06/29/2007 00:00:00
Burland 05/25/2006 00:00:00
even tho I just want the first one with the max date.