Below is the sample data:

Here, Payment due is calculated as -

if 
    ('payment made' previous month < 'Payment due' previous month) 
then 
    'payment due' previous month   'Payment required' current month - 'Payment made' previous month
else
    'Payment required' current month
END if

I know that i can use 'Lag' to get the previous month's value of payment made, but how do i do it for the calculated column - Payment Due?

CodePudding user response：

CTE with recursion might work assuming months start from 1

with recursive cte 
as (
  select month, payment_made, payment_required, 0 payment_due
  from data
  where month = 1
  union
  select b.month, b.payment_made, b.payment_required, (
    case when a.payment_made < a.payment_due
       then a.payment_due   b.payment_required - a.payment_made
       else b.payment_required
       end
  ) 
  from cte a
  join data b on a.month   1 = b.month
)
select * from cte

or recursion on row_number

with recursive 
seq as ( 
  select *, row_number() over(order by month) rn
  from data
),
cte as (
  select seq.rn, seq.month, seq.payment_made, seq.payment_required, seq.payment_required payment_due
  from seq
  where seq.rn = 1
  union
  select b.rn, b.month, b.payment_made, b.payment_required, (
    case when a.payment_made < a.payment_due
       then a.payment_due   b.payment_required - a.payment_made
       else b.payment_required
       end
  ) 
  from cte a
  join seq b on a.rn   1 = b.rn
)
select * from cte 

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=40b4da5924ea279d6c3d3c1a0e0cb1cd

CodePudding user response：

This would work because month on your exampole starts with1 1 and increase by one, if the data isn't that smooth, you must change the subquery in the update to fit better

CREATE TABLE payments (
  `Month` INTEGER,
  `Payment Made` INTEGER,
  `Payment Required` INTEGER,
  `payment Due` INTEGER
);

INSERT INTO payments
  (`Month`, `Payment Made`, `Payment Required`, `payment Due`)
VALUES
  ('1', '0', '0', '0'),
  ('2', '200', '190', '190'),
  ('3', '500', '190', '190'),
  ('4', '0', '190', '190'),
  ('5', '0', '190', '380'),
  ('6', '200', '190', '570'),
  ('7', '200', '190', '560');

UPDATE payments as pay1 
,
(SELECT 
p2.`Month`,
if 
    (p1.`Payment Made`  < p1.`payment Due`, 

    p1.`payment Due`   p2.`Payment Made` - p1.`Payment Made`
,
    p2.`Payment Required`
) payment_due
FROM payments p1 INNER JOIN payments p2 ON p2.`Month` = p1.`Month`   1) pay2 
SET pay1.`payment Due` = pay2.payment_due
WHERE pay1.`Month` = pay2.`Month`

SELECT * FROM payments

Month | Payment Made | Payment Required | payment Due
----: | -----------: | ---------------: | ----------:
    1 |            0 |                0 |           0
    2 |          200 |              190 |         190
    3 |          500 |              190 |         190
    4 |            0 |              190 |         190
    5 |            0 |              190 |         190
    6 |          200 |              190 |         580
    7 |          200 |              190 |         570

db<>fiddle here