Find the most recent renewal record in a group of many

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.
meldraperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dinesh manikkamCommented:
Execute this SQL Query from your application.
SELECT
   GRADUATE_ID,
   CERTIFICATION_TYPE,
   RENEWAL_DATE
FROM
   GRADUATES
WHERE
   RENEWAL_DATE <= GETDATE()

Open in new window

meldraperAuthor Commented:
Then I get ALL of the records.  I don't want them all.  I just want the most recent.  If the grad has four renewal records for the same certification going back to 2014, I only want to see the most recent.  I know it's complicated, I was hoping I explained it better.  Thank you.
Jim HornSQL Server Data DudeCommented:
So if I'm interpreting this correctly, the requirement is 'Give me the latest certification date for a given ID-Certification Date that is less than today, as long as there is not a certification date greater than today' ?

SELECT
   g.GRADUATE_ID,
   g.CERTIFICATION_TYPE,
   MAX(g.RENEWAL_DATE) as RENEWAL_DATE_MAX
FROM
   GRADUATES g
   LEFT JOIN (
      -- Include in the return set ONLY those with future renewal dates
      SELECT DISTICNT GRADUATE_ID, CERTIFICATION_TYPE, COUNT(CERTIFICATION_TYPE) as gf_count
      FROM GRADUATES
      WHERE RENEWAL_DATE  > GETDATE() 
      GROUP BY GRADUATE_ID, CERTIFICATION_TYPE) gf 
         ON g.GRADUATE_ID = gf.GRADUATE_ID AND g.CERTIFICATION_TYPE = gf.CERTIFICATION_TYPE
WHERE
   g.RENEWAL_DATE <= GETDATE()
   AND gf_count = 0
GROUP BY g.GRADUATE_ID, g.CERTIFICATION_TYPE

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

meldraperAuthor Commented:
Yes Jim Horn.  Let me try what you have provided.  It looks good at first glance.  Thank you.
meldraperAuthor Commented:
Oh this is so close.  one thing I did not make clear is if they've renewed, they have a current record greater than getdate().  So how do I omit the grad who is current and has renewed?  This is where I get very confused.
Jim HornSQL Server Data DudeCommented:
Don't know what to tell you here, as experts here are not good at defining an asker's requirements.   My code *should* exclude from the return set any ID-certification with a renewal date greater then today, that's what the JOIN (subquery) is for.

Although now that I think of it, change the JOIN to a LEFT JOIN, and the WHERE ..  AND gf_count >= 0 should probably be = 0.  (edited in above code)
meldraperAuthor Commented:
I could also do the opposite and save both as views and join them that way and then do the rest in ASP with if statements...  What you've provided as helped me immensely.  Now I have enough to finish the job.  Many thanks Jim Horn.
meldraperAuthor Commented:
Excellent answer.  Many thanks!
Jim HornSQL Server Data DudeCommented:
Thanks for the grade, good luck with your project.  -Jim
meldraperAuthor Commented:
Jim, just a follow up.  I removed the <=getdate() and let the user put in a date range on the client side.  it works PERFECTLY.  many thanks again for your help.
Jim HornSQL Server Data DudeCommented:
Good deal.  Just for kicks and giggles show me the date range sql.   If it helps Paul Maxwell wrote an excellent article dealing with SQL Server date ranges called Beware of Between.
meldraperAuthor Commented:
Thanks!! I don't use between because I've noticed it doesn't use the start date and end date.   Only what's "between".  I use
where (certexpdate >='" & request.form("startdate") & " AND  certexpdate <='" & request.form("enddate") & "')

Open in new window

.  They tested it this morning and said it's perfect.  I'm so happy this monkey is off my back, lol.
Jim HornSQL Server Data DudeCommented:
Okay, you're good then.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.