SQL Server splits the total refund amount into as few orders as possible

Time:2021-9-9

1、 Question

There were three recharge orders, and now we have to refund 450 yuan. How can we allocate them to minimize the number of recharge orders involved in this refund? Refer to the following figure for specific data:

2、 Solution

Step 1: arrange the refundable amount in descending order to give priority to orders with large refundable amount

Step 2: use CTE common expression to realize functions similar to for or while loop or cursor

3、 Script

create table #t
(
  Recharge int,
  Retired int,
  Refundable int
)
Insert into #t (recharge, refunded, refundable)
values (200, 100, 100), (500, 200, 300), (300, 100, 200)

/*
Author: zhang502219048
Script source: https://www.cnblogs.com/zhang502219048/p/14127208.html
*/

Declare @i to retreat int = 450;
with cte1 as
(
  select *, row_ Number () over (order by refundable DESC) Rn, 0 to initiate refund, 0 to be refunded
  from #t
),
cte2 as
(
  Select RN, recharge, refunded, refundable, 
    Refundable = case when @ I want to refund > refundable then refundable else @ I want to refund end, 
    To be refunded = @ I to be refunded - case when @ I to be refunded > refundable then refundable else @ I to be refunded end -- to be refunded = to be refunded - refund can be initiated
  from cte1
  where rn = 1
  union all
  Select t2.rn, T2. Recharge, T2. Refunded, T2. Refundable,
    Refundable = case when T1. To be refunded > T2. Refundable then T2. Refundable else T1. To be refunded end, 
    To be retired = T1. To be retired - case when T1. To be retired > T2. To be retired then T2. To be retired else T1. To be retired end
  from cte1 t2
  Inner join cte2 T1 on t1.rn = t2.rn - 1 -- T2 is the next record of T1
  --Where T2. RN > 1 and T1. To be returned > 0
)
select * from cte2

drop table #t

4、 Script run results

summary

This is the end of this article about SQL Server splitting the total refund amount into as few orders as possible. For more information about splitting the total refund amount of SQL server into orders, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!