troubleshooting Question

Find the most recent renewal record in a group of many

Avatar of meldraper
meldraperFlag for United States of America asked on
ASPVB ScriptMicrosoft SQL Server
13 Comments1 Solution79 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros