sql server - SQL Subtruct Quantity from the MAX Calculated Value Row with values from another table -
please resolve this,
i trying figure out how update table2 using qtytosubtract , product_id table1, recalculating currentqty , balance every time row updated.
for each row in table1 need update current qty , balance in table2 subtracting currentqty using row max balance product_id. means same record can updated several times if after subtracting holds max balance still.
table1 holds qty subtract:
product_id qtytosubtract 11111111 2 11111111 2 11111111 2 22222222 6 22222222 6 table2 needs updated
product_id originalqty currentqty balance = initial+ current 11111111 46 12 58 11111111 15 40 55 22222222 6 1 7 22222222 24 6 28 22222222 15 8 23 result should :
product_id originalqty currentqty balance = initial+ current 11111111 46 8 54 11111111 15 38 53 22222222 6 1 7 22222222 24 0 22 22222222 15 2 17
ok, 1 of rare times cursor seems best choice:
declare @product_id varchar(8), @qty int declare cc cursor local static read_only forward_only select * table1 order [product_id], [qtytosubtract] desc open cc fetch next cc @product_id, @qty while @@fetch_status = 0 begin cte ( select *, rn=row_number() over(order balance desc) table2 product_id = @product_id ) update cte set balance = balance - @qty rn = 1 fetch next cc @product_id, @qty end close cc deallocate cc select * table2 results:
╔════════════╦═════════════╦════════════╦═════════╗ ║ product_id ║ originalqty ║ currentqty ║ balance ║ ╠════════════╬═════════════╬════════════╬═════════╣ ║ 11111111 ║ 46 ║ 12 ║ 54 ║ ║ 11111111 ║ 15 ║ 40 ║ 53 ║ ║ 22222222 ║ 6 ║ 1 ║ 7 ║ ║ 22222222 ║ 24 ║ 6 ║ 22 ║ ║ 22222222 ║ 15 ║ 8 ║ 17 ║ ╚════════════╩═════════════╩════════════╩═════════╝ and here demo of this.
Comments
Post a Comment