How to calculate the average value of following n rows based on another column – SQL (Oracle) – updated version of previously answered question

I am trying to calculate average monthly value of PREMIUM for each POLICY_ID in monthly basis as shown below screenshot. When a customer updates his/her yearly PAYMENT_FREQUENCY to a value different than 12, I need to manually calculate the average monthly value for the PREMIUM. In addition, average monthly PREMIUM amount can be changed in time. For instance, for the POLICY_ID = 1, starting from “2015/11/01” average monthly premium increased from 120 to 240.

How can I achieve the values shown in the column named MONTHLY _PREMIUM_DESIRED?

(Early version of this question was replied here: )

Note: Oracle version 12c

What I’ve tried:

SELECT 
    T.*,
    SUM(PREMIUM) OVER(PARTITION BY T.POLICY_ID ORDER BY T.POLICY_ID, T.PAYMENT_DATE ROWS BETWEEN CURRENT ROW AND 12/T.YEARLY_PAYMENT_FREQ-1 FOLLOWING ) / (12/T.YEARLY_PAYMENT_FREQ) MONTLY_PREMIUM_CALCULATED
FROM MYTABLE2 T
;

Codde for data:

DROP TABLE MYTABLE2;
CREATE TABLE MYTABLE2 (POLICY_ID NUMBER(11), PAYMENT_DATE DATE, PREMIUM NUMBER(5), YEARLY_PAYMENT_FREQ NUMBER(2),MONTHLY_PREMIUM_DESIRED NUMBER(5));
INSERT INTO MYTABLE2 VALUES (1, DATE '2014-10-01',120,12,120);
INSERT INTO MYTABLE2 VALUES (1, DATE  '2014-11-01',360,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2014-12-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-01-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-02-01',360,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-03-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-04-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-05-01',720,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-06-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-07-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-08-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-09-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-10-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-11-01',240,12,240);
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-12-01',240,12,240); 
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-01-01',960,4,240);     
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-02-01',0,4,240);   
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-03-01',0,4,240);    
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-04-01',0,4,240);  
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-05-01',960,4,240);     
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-06-01',0,4,240);     
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-07-01',0,4,240);      
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-08-01',0,4,240);    
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-01-01',60,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-02-01',0,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-03-01',0,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-04-01',0,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-05-01',180,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-06-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-07-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-08-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-09-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-10-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-11-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-12-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-01-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-02-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-03-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-04-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-05-01',15,12,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-06-01',15,12,15);
SELECT * FROM MYTABLE2;

Answer

I think what you are after is this:

select t.*, 
       last_value(nullif(premium * yearly_payment_freq / 12, 0) ignore nulls) 
                   over (partition by policy_id order by payment_date)
                   as monthly_premium_calculated
from   mytable2 t
;

For a month with a non-zero payment, this takes the premium paid, it multiplies by the annual frequency to get an equivalent annual premium, and it divides the result by 12 to get a monthly equivalent. For a month where the premium is zero, the assumption is that that month’s premium was paid in an earlier month (with a frequency less than “monthly” – that is, the premium was paid for a full quarter, or half-year, or year).

Running this on your data I found a mistake in your “desired” data. For POLICY_ID = 1, for ‘2016-01-01’, you show payment of 960 and annual frequency of 4, and “desired monthly amount” of 240. Isn’t that wrong? Frequency of 4 means “quarterly”, and if the quarterly amount is 960, then the monthly amount is 320, not 240. Right? My computation gets the result 320.

There is also a typo in your inputs: you have a row for December 2016 for policy id 1, when you meant December 2015. I meant to edit your post to correct that, but then I realized the image you posted is based on the wrong data (with that typo). If I only change it in the INSERT statements, then they won’t match the image…