Given a fixed date, for instance
27/04, what would be the best way to get the most recent year that the aforementioned date happened before another given date?
Date A: 27/04 Date B: 25/05/2016 Result: 27/04/2016 Date A: 27/04 Date B: 25/04/2016 Result: 27/04/2015
A bit of background as to why we need this:
I'm correcting an issue in a report to do with insurance claims for the broker I work for. The report joins a few tables, but the two relevant ones to this question are
policies which stores the start date of a policy, and
reportedclaims which stores the claims that have been reported while the client has been insured with us.
What we need is the start date of the most recent policy year.
Each year when a policy is renewed its start date is advanced by one year. This means that when we run this report the start date of the policy may be after the claim date, which makes no sense. We don't store the original start date, but even if we did we'd have a similar problem if a claim came in 3 or 4 years after the policy was first incepted:
Original start date: 27/04/2012 Current start date: 27/04/2016 Claim date: 26/04/2016 Required start date: 27/04/2015
I could do the calculation in PHP, but since the rest of the report is being created in MySQL I'd prefer not to do that if I can avoid it.
We are currently using a column call
uyear which stores the underwriting year that the claim falls under. This is ideal for the task, and saves any weighty calculations in the SQL or the script. Unfortunately I've just found out that this data is unreliable as the people entering it are quite often entering the incorrect year.