query to get max year

[JP]

I have a table with a field name period

Data is in yyyymm format

200712
200711
200809
200807

I need to extract the maximum year for years that have a full year of data.
In the sample data above,
2008 data only goes to September, while 2007 data goes to December, therefor
I need the query to return '2007' in this instance.

 

[Carl Rapson]

Try something like:

SELECT MAX(LEFT(PERIOD,4)) FROM

WHERE PERIOD LIKE "*12"

Carl Rapson

 

[John Spencer]

What if 2007 didn't have a 200710 record. Would you need to skip that
and go back to 2006?

Does your table only allow one record per month and year combination?

SELECT TOP 1 Left(Period,4) as LatestYear
FROM SomeTable
GROUP BY Left(Period,4)
HAVING Count(*) = 12
ORDER BY Period DESC

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================