We help IT Professionals succeed at work.
Get Started

Find the most recent renewal record in a group of many

Last Modified: 2016-02-10
I have a table called graduates, there are 4000 records.  The related table is the renewal table for these graduates and there are many thousand of these.  There are three different types of certifications.  AAA, BBB and CCC.  BUT there is only one graduate ID and each graduate could have up to three certifications.  The renewal records append so let's say graduate 123456 has two certifications but only AAA needs to be renewed and their first renewal goes back to 2014.  So I have three  renewal records for 123456 and they are up for renewal in 2016.  It would look something like this for grad 123456, but there could be as many as three certification types for each grad and many renewal years for 4000 grads.

123456            2014-04-29       BBB
123456            2015-04-29       BBB
123456            2016-04-29       BBB
123456            2017-04-29       BBB
123456            2014-01-02       AAA
123456            2015-01-02       AAA
123456            2016-01-02       AAA

Here's what I need to accomplish.  A report that shows the most recent renewal record for each grad for each certification type and display only the records that are not greater than today's date.  in this case, >getdate().  I just can't seem to wrap my brain around how to OMIT the past years.  Classic ASP, VBSCRIPT, MSSQL.  Many thanks in advance.
Watch Question
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE