I have one table as below mysql
customer_id,invoice_id,invoice_month,payment_month,invoice_amount,payment_amt,balance_amt
1,1,"DEC 23","JAN 24",400.00,400.00,0.00
1,2,"DEC 23","JAN 24",600.00,600.00,0.00
1,3,"JAN 24","JAN 24",200.00,50.00,150.00
2,4,"SEP 23","MAY 24",800.00,800.00,0.00
2,5,"OCT 23","MAY 24",750.00,750.00,0.00
3,6,"FEB 24","MAY 24",925.00,525.00,400.00
4,7,"NOV 22","JUN 23",325.00,325.00,0.00
4,8,"DEC 22","JUN 23",425.00,425.00,0.00
5,9,"JUN 24",NULL,500.00,NULL,500.00
Payment table as
customer_id,payment_month,amount
1,JAN 24,500
1,FEB 24,550
2,MAR 24,450
2,APR 24,900
2,MAY 24,450
3,MAY 24,525
4,JUN 23,350
4,JUN 23,400
need to create a new columns payment_final_month based on "payment_month" from the payment table. For the first record i have invoice amount has 400 which is less than the payment table amount first record then pick JAN 24 For the second record as i have 100 left from the first record then pick the same JAN 24 For 3rd record i should pick the FEB 24
Final output something as
customer_id invoice_id invoice_month payment_month invoice_amount payment_amt balance_amt payment_final_month
1 1 DEC 23 JAN 24 400.00 400.00 0.00 JAN 24
1 2 DEC 23 JAN 24 600.00 600.00 0.00 JAN 24
1 3 JAN 24 FEB 24 200.00 50.00 150.00 FEB 24
2 4 SEP 23 MAR 24 800.00 800.00 0.00 MAR 24
2 5 OCT 23 APR 24 750.00 750.00 0.00 APR 24
3 6 FEB 24 MAY 24 925.00 525.00 400.00 MAY 24
4 7 NOV 22 JUN 23 325.00 325.00 0.00 JUN 23
4 8 DEC 22 JUN 23 425.00 425.00 0.00 JUN 23
5 9 JUN 24 NULL 500.00 NULL 500.00 NULL
I tried below approach and didnt worked
i.invoice_amount - COALESCE(LAG(i.payment_amt, 1) OVER (PARTITION BY i.customer_id ORDER BY i.invoice_id), 0) AS remaining_amount
This is a bit messy cause there are some design issues regarding keys, relations and (especialy) dates.
You should convert your month strings into date datatypes to solve this. One way to do it is using cte for date datatype conversions and calculating running summs (cumulatives) for invoice and payment amounts.
So, with your sample data:
WITH -- S a m p l e D a t a :
invoices (customer_id, invoice_id, invoice_month, payment_month,
invoice_amount, payment_amt, balance_amt) AS
( Select 1, 1, 'DEC 23', 'JAN 24', 400.00, 400.00, 0.00 Union All
Select 1, 2, 'DEC 23', 'JAN 24', 600.00, 600.00, 0.00 Union All
Select 1, 3, 'JAN 24', 'JAN 24', 200.00, 50.00, 150.00 Union All
Select 2, 4, 'SEP 23', 'MAY 24', 800.00, 800.00, 0.00 Union All
Select 2, 5, 'OCT 23', 'MAY 24', 750.00, 750.00, 0.00 Union All
Select 3, 6, 'FEB 24', 'MAY 24', 925.00, 525.00, 400.00 Union All
Select 4, 7, 'NOV 22', 'JUN 23', 325.00, 325.00, 0.00 Union All
Select 4, 8, 'DEC 22', 'JUN 23', 425.00, 425.00, 0.00 Union All
Select 5, 9, 'JUN 24', NULL, 500.00, NULL, 500.00
),
payments (customer_id, payment_month, amount) AS
( Select 1, 'JAN 24', 500 Union All
Select 1, 'FEB 24', 550 Union All
Select 2, 'MAR 24', 450 Union All
Select 2, 'APR 24', 900 Union All
Select 2, 'MAY 24', 450 Union All
Select 3, 'MAY 24', 525 Union All
Select 4, 'JUN 23', 350 Union All
Select 4, 'JUN 23', 400
),
... first create a cte that will be used for months to be converted into dates...
-- months conversion cte
dates (mon, mm) AS
( Select 'JAN', '01' Union All
Select 'FEB', '02' Union All
Select 'MAR', '03' Union All
Select 'APR', '04' Union All
Select 'MAY', '05' Union All
Select 'JUN', '06' Union All
Select 'JUL', '07' Union All
Select 'AUG', '08' Union All
Select 'SEP', '09' Union All
Select 'OCT', '10' Union All
Select 'NOV', '11' Union All
Select 'DEC', '12'
),
... next, create two more ctes for date conversion (using above cte) and for running summs of invoice and payment amounts ...
-- prepare payment data (cumulative sums and dates)
payments_cumul AS
( Select p.customer_id, p.payment_month, p.amount,
STR_TO_DATE( Concat('01', d.mm, SubStr(p.payment_month, 5, 2)), '%d%m%y' ) as pay_date,
Sum(p.amount) Over(Partition By p.customer_id
Order By STR_TO_DATE( Concat('01', d.mm, SubStr(p.payment_month, 5, 2)), '%d%m%y' )
Rows Between Unbounded Preceding And Current Row) as pay_amt_cumul
From payments p
Inner Join dates d ON( d.mon = SubStr(p.payment_month, 1, 3))
),
-- prepare invoice data (cumulative sums and dates)
grid as
( Select i.customer_id, i.invoice_id,
i.invoice_month, i.payment_month as inv_payment_month,
i.invoice_amount,
Sum(i.invoice_amount) Over( Partition By i.customer_id, i.payment_month
Order By STR_TO_DATE( Concat('01', d.mm, SubStr(i.payment_month, 5, 2)), '%d%m%y' )
Rows Between Unbounded Preceding And Current Row
) as inv_amt_cumul,
i.payment_amt, i.balance_amt,
STR_TO_DATE( Concat('01', d.mm, SubStr(i.payment_month, 5, 2)), '%d%m%y' ) as inv_pay_date
From invoices i
Left Join dates d ON( d.mon = SubStr(i.payment_month, 1, 3) )
)
... finaly join it all together, aggregate and group by...
-- M a i n S Q L :
Select g.customer_id, g.invoice_id,
Max(g.invoice_month) as invoice_month,
g.inv_payment_month,
g.invoice_amount, g.payment_amt, g.balance_amt,
DATE_FORMAT(Case When g.inv_amt_cumul - Max(Coalesce(p.pay_amt_cumul, 0)) > 0
Then Coalesce(Max(p.pay_date), g.inv_pay_date)
Else g.inv_pay_date
End, '%M %y') as payment_final_month
From grid g
Left Join payments_cumul p ON( p.customer_id = g.customer_id And
g.inv_amt_cumul >= p.pay_amt_cumul
)
Group By g.customer_id, g.invoice_id, g.inv_payment_month, g.inv_pay_date,
g.invoice_amount, g.payment_amt, g.balance_amt
Order By g.customer_id, g.invoice_id
/* R e s u l t :
customer_id invoice_id invoice_month inv_payment_month invoice_amount payment_amt balance_amt payment_final_month
----------- ---------- -------------- ------------------ -------------- ----------- ----------- -------------------
1 1 DEC 23 JAN 24 400.00 400.00 0.00 January 24
1 2 DEC 23 JAN 24 600.00 600.00 0.00 January 24
1 3 JAN 24 JAN 24 200.00 50.00 150.00 February 24
2 4 SEP 23 MAY 24 800.00 800.00 0.00 March 24
2 5 OCT 23 MAY 24 750.00 750.00 0.00 April 24
3 6 FEB 24 MAY 24 925.00 525.00 400.00 May 24
4 7 NOV 22 JUN 23 325.00 325.00 0.00 June 23
4 8 DEC 22 JUN 23 425.00 425.00 0.00 June 23
5 9 JUN 24 null 500.00 null 500.00 null */
See the fiddle here.
UPDATE: ( Error Code: 1270. Illegal mix of collations )
mixed collation within Concat() function causes the error. First two strings in concat are utf8 and the third one is latin1. Use explicite collation either on first two or on the third and make them all the same (all 4 Concatinations in code):
STR_TO_DATE( Concat('01', -- utf8
d.mm, -- utf8
SubStr(p.payment_month COLLATE utf8mb4_0900_ai_ci, 5, 2)),
'%d%m%y' )
OR
STR_TO_DATE( Concat('01' COLLATE latin1_swedish_ci,
d.mm COLLATE latin1_swedish_ci,
SubStr(p.payment_month, 5, 2)), -- latin1
'%d%m%y' )