It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate. I need help ASAP. Are there any savy users out there that could help?
Here is the first join I have....
SELECT DT.ClientID, Year1 FROM
SELECT ClientID, YEAR(MyDate) as Year1
) AS DT
GROUP BY ClientID, Year1
ORDER BY ClientID, Year1
I want to use that as my derived table and then join back to it... but it's not working... Any help?
SELECT DT.ClientID, MIN(MyDate) AS MyDate, MyYear FROM
SELECT ClientID, YEAR(MyDate) as MyYear
) AS DT
INNER JOIN MyTable on MyTable.ClientID = DT.ClientID AND YEAR(MyTable .MYDate) = MYYear
GROUP BY DT.ClientID, MyYear
ORDER BY ClientID, MyYear