Hi,
I have to do an interest calculation on additional principal that an employee has to pay with effect from a prior date. The data is attached for two employees spanning three financila years.. Financial year is from April to March
Following is the expected result for employee with ID 100
In the above data, last five columns are the desired result. Interest is calculated on the cumulative value of additional_principal_amount. The cumulative value of additional_principal_amount is the sum of additional_principal_amount till previous month. But when every new financial year starts, in addition to the sum of additional_principal_amount till previous month, the total interest amount on additional principal, for the previous financial year will be added. From the above data, interest total for financial year 95-96 is 12 which is added to the cumulative for the next financial year 96-97.interest total for financial year 96-97 is 355 which is added to the cumulative for the next financial year 96-97 and so on.
Eg: Please check row 5 (01/04/1996). The cumulative is 110+300+300+314+ 12(Interest total for fin yr 95-96)
From september 1996 onwards another contribution is also to be deducted which is 20 % of the additional_principal_amount and cumulative amount and interest to be calculated using same logic
Is it possible to get the desired result in the above included image through a query or should i write a procedure for the same?
Please help.
Table structure and data are attached.