I have a SQL table that looks like this

CREATE TABLE #Temp_Supply 
(
    Supplier_Name Varchar(30), 
    Invoice_Number Varchar(20), 
    Amount Money
)

INSERT INTO #Temp_Supply (Supplier_Name, Invoice_Number, Amount)
    SELECT 'Supply1', 'Inv1', 1000 UNION
    SELECT 'Supply1', 'Inv2', 2000 UNION
    SELECT 'Supply1', 'Inv3', 3000 UNION
    SELECT 'Supply2', 'Inv1', 1500 UNION
SELECT 'Supply2', 'Inv2', 2500
    
CREATE TABLE #Temp_Supply_Deduction
(
    Supplier_Name Varchar(30), 
    Deduction_Amount Money
)

INSERT INTO #Temp_Supply_Deduction(Supplier_Name, Deduction_Amount)
    SELECT 'Supply1', 2500 UNION
SELECT 'Supply2', 500
    
SELECT * FROM #Temp_Supply
SELECT * FROM #Temp_Supply_Deduction

While selecting the supply table To deduct the Hold amount starting with the smallest Invoice Number until the amount is exhausted

The excepted output should be

Thanks

CodePudding user response：

use window function sum() over (partition by ... order by ...) to get the cumulative sum of Amount. Use CASE expression to conditionally check for Deduction_Amount against the cumulative sum amount to determine the balance amount

select d.Supplier_Name, 
       d.Deduction_Amount, 
       s.Invoice_Number, 
       Invoice_Amount = s.Amount,
       Balance = case when  d.Deduction_Amount
                      >=    sum(s.Amount) over (partition by d.Supplier_Name 
                                                order by s.Amount, 
                                                         s.Invoice_Number)
                      then  0
                      when  d.Deduction_Amount 
                      >=    sum(s.Amount) over (partition by d.Supplier_Name 
                                                    order by s.Amount, 
                                                             s.Invoice_Number)
                      -     s.Amount
                      then  sum(s.Amount) over (partition by d.Supplier_Name 
                                                    order by s.Amount, 
                                                             s.Invoice_Number)
                      -     d.Deduction_Amount
                      else  s.Amount
                      end
from   #Temp_Supply_Deduction d
       inner join #Temp_Supply s on d.Supplier_Name = s.Supplier_Name

db<>fiddle demo

By the way, your expected output in the question is wrong

Should be