5 Replies Latest reply on Jul 23, 2015 12:38 PM by lijnbach

    Calculating renewall (prolongation) date

    lijnbach

      Title

      Calculating renewall (prolongation) date

      Post

      Hello everybody,

       

      I have a date calculation, and I can’t figure out the best way to handle this. So what’s the problem?

       

      I have a database with contracts. The contracts have a first period, an auto renewal period and a resignation period. But different for each contract. (The user has over 800 machines with a service contract, but all different contracts, with different renewal periods and resignation periods. The user wants to know every week when he has to do resignations)

       

      e.g.

      Starting first periode:                      01-01-2012

      First periode:                                  12 months

      Ending first periode:                       12-31-2012

       

      Resignation:                                   3 months

      Ultimate date for first resignation:  09-31-2012

       

      First renewal (prolongation):          01-01-2013

      Renewal period:                             6 months

      Renewal end:                                06-30-2013

      Ultimate date for resignation:        03-31-2013

       

      Next renewal:    (In 2015)              This is the problem.

      Next renewal end:

      Ultimate dat for next resignation:   (It is all about this date for the user)

       

      With older contracts there already has been a renewal for 5 or 10 times. In the example there already has been 5 automatic renewals after the first period.

       

      I need a calculation that calculates the next renewal based on todays date.

       

      I have a solution, but I don’t like my own solution. It is a script with a loop that keeps adding renewal periods until the appropriate date. But that means there has to be a complete recalculation each time the user loads a record or changes a record. And there has to be complete recalculation when the user prints a list with resignation dates for the coming week.

       

      Anyone a better idea?

       

      Thanks in advance,

       

      Hans Lijnbach

       

       

        • 1. Re: Calculating renewall (prolongation) date
          SteveMartino

          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

          • 2. Re: Calculating renewall (prolongation) date
            lijnbach

            Hai Steve,

            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.

            Hans Lijnbach

            • 3. Re: Calculating renewall (prolongation) date
              lijnbach

              O.K. Steve,

              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,

              Hans Lijnbach

              • 4. Re: Calculating renewall (prolongation) date
                SteveMartino

                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

                Start Date

                Duration

                End Date

                Resignation

                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.

                 

                • 5. Re: Calculating renewall (prolongation) date
                  lijnbach

                  O.K. Steve,

                  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.

                  Thanks Steve,

                  Hans Lijnbach