clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591601 fiddles created (45745 in the last week).

CREATE TABLE MYTABLE (POLICY_ID int, PAYMENT_DATE DATE, PREMIUM int, YEARLY_PAYMENT_FREQ int,MONTHLY_PREMIUM_DESIRED int);
 hidden batch(es)


INSERT INTO MYTABLE VALUES (1, DATE '2014-10-01',120,12,120); INSERT INTO MYTABLE VALUES (1, DATE '2014-11-01',360,4,120); INSERT INTO MYTABLE VALUES (1, DATE '2014-12-01',0,4,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-01-01',0,4,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-02-01',360,4,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-03-01',0,4,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-04-01',0,4,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-05-01',720,2,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-06-01',0,2,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-07-01',0,2,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-08-01',0,2,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-09-01',0,2,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-10-01',0,2,120); INSERT INTO MYTABLE VALUES (1, DATE '2015-11-01',120,12,120); INSERT INTO MYTABLE VALUES (2, DATE '2015-01-01',60,3,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-02-01',0,3,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-03-01',0,3,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-04-01',0,3,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-05-01',180,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-06-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-07-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-08-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-09-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-10-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-11-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2015-12-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2016-01-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2016-02-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2016-03-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2016-04-01',0,1,15); INSERT INTO MYTABLE VALUES (2, DATE '2016-05-01',15,12,15); INSERT INTO MYTABLE VALUES (2, DATE '2016-06-01',15,12,15);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


select t.*, ( max(premium) over (partition by policy_id, grp) / (12 / yearly_payment_freq) ) as monthly_premium_calculated from (select t.*, sum(case when premium > 0 then 1 else 0 end) over (partition by policy_id order by payment_date) as grp from mytable t ) t
policy_id payment_date premium yearly_payment_freq monthly_premium_desired grp monthly_premium_calculated
1 2014-10-01 120 12 120 1 120
1 2014-11-01 360 4 120 2 120
1 2014-12-01 0 4 120 2 120
1 2015-01-01 0 4 120 2 120
1 2015-02-01 360 4 120 3 120
1 2015-03-01 0 4 120 3 120
1 2015-04-01 0 4 120 3 120
1 2015-05-01 720 2 120 4 120
1 2015-06-01 0 2 120 4 120
1 2015-07-01 0 2 120 4 120
1 2015-08-01 0 2 120 4 120
1 2015-09-01 0 2 120 4 120
1 2015-10-01 0 2 120 4 120
1 2015-11-01 120 12 120 5 120
2 2015-01-01 60 3 15 1 15
2 2015-02-01 0 3 15 1 15
2 2015-03-01 0 3 15 1 15
2 2015-04-01 0 3 15 1 15
2 2015-05-01 180 1 15 2 15
2 2015-06-01 0 1 15 2 15
2 2015-07-01 0 1 15 2 15
2 2015-08-01 0 1 15 2 15
2 2015-09-01 0 1 15 2 15
2 2015-10-01 0 1 15 2 15
2 2015-11-01 0 1 15 2 15
2 2015-12-01 0 1 15 2 15
2 2016-01-01 0 1 15 2 15
2 2016-02-01 0 1 15 2 15
2 2016-03-01 0 1 15 2 15
2 2016-04-01 0 1 15 2 15
2 2016-05-01 15 12 15 3 15
2 2016-06-01 15 12 15 4 15
 hidden batch(es)