I need to insert new quantities to the stock table, and when on insertion closing stock should be reset. My stock table as

INSERT INTO `a1` (`id`, `trs_date`, `product_id`, `qty_in`, `qty_out`, `qty_bal`) VALUES
(1, '2022-01-01', 1, 10, 0, 10),
(2, '2022-01-01', 2, 20, 0, 20),
(3, '2022-01-02', 1, 5, 0, 15),
(4, '2022-01-02', 2, 3, 0, 23),
(5, '2022-01-03', 1, 0, 8, 7);

New data will be taken from table a2 as

INSERT INTO `a2` (`id`, `trs_date`, `product_id`, `qty_in`, `qty_out`) VALUES
(1, '2022-01-04', 1, 50, 0),
(2, '2022-01-04', 2, 60, 0),
(3, '2022-01-05', 1, 0, 10);

I need to add data from a2 to a1. When inserting qty_bal should be updated as
qty_bal = previous_qty_bal quantity_in - quantity_out for each product. My code as follows

    INSERT INTO 
    a1 (trs_date, product_id, qty_in, qty_out, qty_bal) 
    ( WITH cte_tbl AS ( 
            (select trs_date, product_id, qty_in, qty_out, qty_bal from ( SELECT *, row_number() over (partition by `product_id` order by `trs_date` desc) rn FROM a1 ) existing_latest_data where rn = 1) 
            
            UNION ALL 
            
            (SELECT trs_date, product_id, qty_in, qty_out, 0 AS qty_bal FROM a2) 
        ) 
        SELECT 
            trs_date, product_id, qty_in, qty_out, 
            lag(qty_bal) over (PARTITION BY product_id ORDER BY trs_date)   qty_in- qty_out as qty_bal 
        FROM cte_tbl ORDER BY trs_date 
    ) 

And it result Table a1 as

My expected result should be as

So what should I change.

CodePudding user response：

1. Get last qty_bal per product (Note to order by trs_date desc, id desc in case more than one row in a day)
2. Union with receives in a2 (added skip_flag)
3. Calculate new qty_bal per row (To order by trs_date, id to get data in proper order)

insert into a1 (trs_date, product_id, qty_in, qty_out, qty_bal)
with cte_balance as (
select id, trs_date, product_id, qty_in, qty_out, qty_bal,
       row_number() over (partition by product_id order by trs_date desc, id desc) as rn
  from a1),
cte_receive as (
select id, trs_date, product_id, 0 as qty_in, 0 as qty_out, qty_bal, rn as skip_flag from cte_balance where rn = 1
 union all
select id, trs_date, product_id, qty_in, qty_out, qty_in-qty_out, 0 from a2),
cte_new_balance as (
select id, trs_date, product_id, qty_in, qty_out, qty_bal,
       sum(qty_bal) over (partition by product_id order by trs_date, id) as new_bal,
       skip_flag
  from cte_receive)
select trs_date, product_id, qty_in, qty_out, new_bal as qty_bal
  from cte_new_balance
 where skip_flag = 0;