pulling data for multiple years

[terpster73]

I’m trying to pull together some data. We have different locations enter in survey data each year. Some locations may be late in entering in their data, and some haven’t entered data for several years. I need to be able to pull the data for the last given year that someone has entered in data.

WestCoast 2003
EastCoast 2008
Southern 2008
Northern 2007
International 2008

let me know if you need aditional information.

 

[RiverGuy]

It's hard to give advice with the information you provided. Just guessing on your schema, you would do something like this:

SELECT b.*
FROM

(SELECT Location, YEAR(MAX(DateColumn)) AS MaxYear
FROM
YourTable
GROUP BY Location) a

INNER JOIN YourTable b
ON YEAR(a.DateColumn) = b.MaxYear

The point here is to get a list of the maximum years and use that to join to your table to filter on just the data you need.

If needed, you could further optimize this query and return a start date of the year and an end date of the year in your subquery and join on a true date.