It's a little hard to follow your example, I can't tell if this is all one record with a bunch of fields, or if you have a product with a one to many to contracts. But let me see if I understand:
Starting first periode: 01-01-2012 A
First periode: 12 months B
Ending first periode: 12-31-2012 C=A+B
Resignation: 3 months D
Ultimate date for first resignation: 09-31-2012 E=C-D
First renewal (prolongation): 01-01-2013 F=C+1 (day)
Renewal period: 6 months G
Renewal end: 06-30-2013 H=G+F
Ultimate date for resignation: 03-31-2013 I=H-3(months) //is it always 3 months or the value in D
Next renewal: (In 2015) This would seem to be a continuations from above based on the end date + 1 day Does renewal mean renewal from last contract (I assume 12-31-2014, lets call it J
We are missing a renewal period: lets say 6 mo K
Next renewal end: L=J+K
Resignation (missing-3 mos?) M or 3 months
Ultimate date for next resignation: N=L-M
Your formula for ultimate date now only has to compare the ultimate date against current date
O.K. It is all in one record, because it all concerns one contract. Every record is a contract.
To start with, yes C = (A + B) - 1 day
And you are right, the renewal keeps going on (in this case for 6 months) until my user ends the contract (with the appropriate resignation date). (In other contracts the renewal can be 12 months or 2 moths, also with different resignation periods). (And the automatic renewal period is not the same as the resignation period. I can have a renewal for eg. 12 months with a resignation period of 4 months).
The point is, I don't need to know the renewals in between, only the first and the actual renewal with the ultimate resignation date, so the user knows what date he can end the contract if needed. If the current date is after the ultimate resignation date of the last renewal, there needs to be a calculation of a new renewal period. (Because he was to late with his resignation).
And yes, the first renewal comes after the first period. That is because mostly (not always) the first period is longer then the automatic renewals. (That had to do with the dutch law). But the renewal is continuation of the contract, but with another period then the first period. (Sometimes).
At the end, the users wants to know which contracts have a ultimate resignation date this week. So he can decide to continue the renewal or end the contract.
Ik hope this clarification helps.
I have been looking it over again, and what you presented is correct and I can work with it. They only thing I am struggling with is the exact calculation of "K". K represents a number of renewal periods, depending of the renewal period, the end of the first period and the current date. Some contracts are already 10 years old (or even older). And I can't find a proper way to calculate the last (active) renewal ("L") based on the number of "K's". Whereby the active renewal is not relevant anymore, if the current date is later then the active resignation date.
Thanks in advance,
You're struggling, and you know what you're talking about...how do you think I feel :)
I assume each renewal creates a new record, So when a new record is created, can you get the value from the previous records renewal end, + one day
I am also assuming each record consists of
Ultimate date for resignation
So when you renew a contract, you create a new record, auto grab some info from the last record, and everything else should populate. Obviously this is a simplification of your very complex issue.
Thanks for your effort and time again,
I was working with just one record, and trying to calculate the number of "K's". But what you assumed, using a new record for each renewal, might be a better solution. At least is will be easy to grab the information from the previous record. It even might be more interesting to work with a portal with related records with the renewals. At least it will be easier to grab the information and the user can see all the renewals. I'll think about how to handle this, new records, and show only the last one, or using a portal with all renewals.