In the insurance area, we work with policies; at the same time, a policy have coverages and each of them have an amount to pay for from the client, know as Premium.
Said that, I've 2 tables: Policy and Coverage, being premium one of the coverage's fields. So, if I need to know the premium of the policy, I'd have to sum the field Premium of all its coverages.
What I want to achieve, is the accrue premium, daily (an accumulative). The logical way to calculate it, is to take the total amount of the premium and divide it in 365 (total days of the year), and from the start day of validity until the present date or its due date, accumulate that daily amount.
For example:
Total Premium: 365.000 $
To each day correspond: 1.000$
Start validity date of the policy: 27/06/2011.
Due date: a year after.
Accrue premium account to present day (27/09/2011): 60.000$ (approximately 60 days, 1.000$ each)
I'm trying to do the mentioned in the Script.
I just created a Accrue Table, where I have the total premium for policy, for year, and for month:
DEVENGADO: //ACCRUE
qualify *;
Directory;
LOAD
COD_POLIZA, //POLICY CODE
ANNO_P, //YEAR
MES_P, //MONTH
COBERTURA.DIA_P AS DIA_P, //DAY
SUM(COBERTURA.PRIMA_COBRAR)/12 AS PRIMA_COBRAR_MONTH
RESIDENT
COBERTURA //COVERAGES
group by ANNO_P, MES_P, COD_POLIZA;
But I need it for day. So, how can I create a table, like a calendar, with the daily amount of all the premiums??? knowing that the distribution must start from de validity date of each policy.
