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.
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.
ASPVB ScriptMicrosoft SQL Server
Last Comment
Jim Horn
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes Jim Horn. Let me try what you have provided. It looks good at first glance. Thank you.
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.
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)
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)
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.
ASKER
Excellent answer. Many thanks!
Thanks for the grade, good luck with your project. -Jim
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.
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.
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") & "')
Okay, you're good then.
Open in new window