Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Need Help with a SPROC to query earliest dates of each year in a table

    Hello. Any help would be appreciated!!!!!

    I have to find the earliest date for any existing calendar year in a table...

    For instance.....

    ClientID Year
    1 1/1/2000
    1 2/1/2000
    2 3/1/2000
    2 4/1/2001
    2 5/1/2001

    The results I need are....


    ClientID Year
    1 1/1/2000
    2 3/1/2000
    2 4/1/2001


    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
    FROM MyTable
    ) 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?

    Thanks!

  2. #2
    Join Date
    Nov 2011
    Posts
    2

    Smile Figured it out in case someone is curious...

    SELECT DT.ClientID, MIN(MyDate) AS MyDate, MyYear FROM
    (
    SELECT ClientID, YEAR(MyDate) as MyYear
    FROM MyTable
    ) AS DT
    INNER JOIN MyTable on MyTable.ClientID = DT.ClientID AND YEAR(MyTable .MYDate) = MYYear
    GROUP BY DT.ClientID, MyYear
    ORDER BY ClientID, MyYear

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •