Find the most recent renewal record in a group of many

meldraper
meldraper used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Execute this SQL Query from your application.
SELECT
   GRADUATE_ID,
   CERTIFICATION_TYPE,
   RENEWAL_DATE
FROM
   GRADUATES
WHERE
   RENEWAL_DATE <= GETDATE()

Open in new window

Author

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.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes Jim Horn.  Let me try what you have provided.  It looks good at first glance.  Thank you.

Author

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 Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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)

Author

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.

Author

Commented:
Excellent answer.  Many thanks!
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade, good luck with your project.  -Jim

Author

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 Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

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 Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Okay, you're good then.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial