0

I'm trying to run a query on some traffic volume data but I'm having some trouble. I would like to pull out the most recent data for each section of road, but this is proving more complicated than expected.

Here is an example of the data I am working with:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1001 | 2/31/2009 | NULL      | 1091
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1235 | 5/10/2011 | South     | 354
1235 | 5/10/2011 | North     | 399
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

I am interested in the returning the most recent AADT (Annual Average Daily Traffic) value for each SectionID and direction. In the data above, I would want the values below returned:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 1/30/2014 | NULL      | 1093

I have the following code:

SELECT  a.*
INTO    recent_data
FROM    primarytrafficcountdata a
INNER
JOIN    (
        SELECT  section_id,
                direction,
                MAX(date) AS maxdate
        FROM    primarytrafficcountdata
        WHERE   YEAR(date) <= 2014
        AND     YEAR(date) >= 2011
        GROUP BY
                section_id,
                direction
        ) groupeda
ON      (a.section_id = groupeda.section_id
AND      a.direction = groupeda.direction
AND      a.date = groupeda.maxdate)
OR      (a.section_id = groupeda.section_id
AND      a.direction IS NULL and groupeda.direction IS NULL
AND      a.date = groupeda.maxdate)
;

But this returns:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

This is the most recent dates, which I want, but I want to eliminate the North/South or East/West directions and only use the NULL directions when the NULL direction is more recent or vice-versa when the North/South or East/West are the most recent records.

Does anyone know how I would do this?

I am using Microsoft Access 2010.

0

Try this:

SELECT  a.* INTO recent_data 
FROM primarytrafficcountdata a 
 INNER JOIN (SELECT section_id,MAX(date) AS maxdate 
              FROM primarytrafficcountdata 
              WHERE YEAR(date) <= 2014 AND YEAR(date) >= 2011 
             GROUP BY section_id) groupeda 
 ON (a.section_id = groupeda.section_id AND a.date = groupeda.maxdate);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.