CODEWITHSUNDEEP
ms-accessms-access-2007
user2388811
user2388811

Reputation: 45

Select Max Date by Date Range

I have a table Inspection and I'm trying to select the max date per date range I want. This is in MS Access 2007 query and most answers I found is in T SQL format.

For example Date Range: Between #1/1/2012# AND #3/30/2012#

Date       PlateNo     TickeStatus
01-15-12       A        Open
01-23-12       c        Open
01-17-12       A        Close
02-23-12       c        Open
02-15-12       A        Open
03-23-12       c        Open
03-15-12       A        Close
03-28-12       c        Close
04-15-12       A        Open
04-23-12       c        Close

In the date range I set. the result I want is like this:

Date       PlateNo     TickeStatus
03-15-12       A        Close
03-28-12       c        Close

Upvotes: 1

Views: 2108

Answers (1)

HansUp
HansUp

Reputation: 97101

This query will give you the maximum Date for each PlateNo within your target date range.

SELECT i.PlateNo, Max(i.Date) AS MaxOfDate
FROM inspections AS i
WHERE i.Date BETWEEN #2012-1-1# AND #2012-3-30#
GROUP BY i.PlateNo;

To get the corresponding TickeStatus values, use that as a subquery which you join to the inspections table.

SELECT i2.Date, i2.PlateNo, i2.TickeStatus
FROM
    inspections AS i2
    INNER JOIN
        (
            SELECT i.PlateNo, Max(i.Date) AS MaxOfDate
            FROM inspections AS i
            WHERE i.Date BETWEEN #2012-1-1# AND #2012-3-30#
            GROUP BY i.PlateNo
        ) AS sub
    ON
            (i2.PlateNo = sub.PlateNo)
        AND (i2.Date = sub.MaxOfDate);

Upvotes: 1

Related Questions