asked on

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.

dinesh manikkam

Execute this SQL Query from your application.

SELECT
   GRADUATE_ID,
   CERTIFICATION_TYPE,
   RENEWAL_DATE
FROM
   GRADUATES
WHERE
   RENEWAL_DATE <= GETDATE()

Open in new window

Mel Parish

ASKER

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mel Parish

ASKER

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

Mel Parish

ASKER

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 Horn

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)

Mel Parish

ASKER

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.

Mel Parish

ASKER

Excellent answer. Many thanks!

Jim Horn

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

Mel Parish

ASKER

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 Horn

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.

Mel Parish

ASKER

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 Horn

Okay, you're good then.