SQL running balance calculation for a partition group

Question

I have a requirement of capping the Qty to certain level. The below data is partitioned and row numbers also provided. Please refer below this is the current data in table

However output should come like this. Please refer below.

The current truck capacity is 16 Qty. But if you see the total Allocation Qty at partition level is 17. So we need to minus that extra 1 Qty from the Final Allocation Qty dynamically at Row level preferable from Rownumber 1..2..3 like that. I had tried to do running total using while loop but no luck

What I have tried to so far

DECLARE @GroupCount INT
SET @GroupCount = (SELECT MAX(PartitionNum) FROM Allocation_Table) 
DECLARE @RowCount INT

-- Declare an iterator
DECLARE @I INT,@J int
 --Initialize the iterator
SET @I =1


WHILE (@I <= @GroupCount)
BEGIN
SET @RowCount = (SELECT MAX(RowNumber) FROM Allocation_Table WHERE PartitionNum=@I)  
DECLARE @BS float=0
SELECT @BS = cast([Remainder Qty to be dropped] as float) FROM Allocation_Table WHERE PartitionNum=@I 
SET @J = 1

WHILE (@J <= @RowCount)
BEGIN
        
--PRINT @I

declare @BV float, @Qty float,@flg bit,@Ibs float, @EV float

SELECT @Qty=[Final Allocation Qty] FROM Allocation_Table WHERE PartitionNum=@I and RowNumber=@J 
set @IBS=@BS
SET @BS=case when (@BS>=@Qty) then  @BS-@Qty  else @BS end

SET @flg=case when (@IBS>=@Qty) then  1  when (@IBS<@Qty) and @IBS>0 then  1 else 0 end

set @BS= case when (@IBS<@Qty)  then 0 else @BS end 

update Allocation_Table set BS_Cal=@BS ,Flag=@flg WHERE  RowNumber = @J and PartitionNum=@I 


SET @J = @J + 1
END
SET @I = @I + 1

Answer 1

Contrary to what has been said in the comments this is very possible using window functions, it is just a little convoluted.

Assuming I have understood your situation correctly, you want to remove items from the material in order of their RowNumber until the Truck capacity is reached. To do this just required some running aggregates and conditional maths based on the value in the previous row:

Query

declare @t table(pn int,rn int,Material varchar(100),Allocation int,Capacity int);
insert into @t values
 (1,1,'abc',4,16)
,(1,2,'bac',9,16)
,(1,3,'cab',4,16)
,(2,1,'abc',4,12)
,(2,2,'bac',9,12)
,(2,3,'cab',4,12)
,(3,1,'abc',4,2)
,(3,2,'bac',9,2)
,(3,3,'cab',4,2)
,(4,1,'abc',14,112)
,(4,2,'bac',19,112)
,(4,3,'cab',14,112)
,(5,1,'abc',140,112)
,(5,2,'bac',19,112)
,(5,3,'cab',14,112)
;

with d as
(
    select *
          ,sum(Allocation) over (partition by pn) as TotalAllocation
          ,sum(Allocation) over (partition by pn) - Capacity as TotalOverage
          ,sum(Allocation) over (partition by pn)
            - Capacity
            - sum(Allocation) over (partition by pn order by rn)
            as Overage
    from @t
)
select pn
      ,rn
      ,Material
      ,Capacity
      ,TotalAllocation
      ,Allocation
      ,case when Overage > 0
            then 0
            else case when lag(Overage,1) over (partition by pn order by rn) is null
                      then case when Allocation < (Allocation - TotalOverage)
                                then Allocation
                                else Allocation - TotalOverage
                                end
                      else 
                            case when lag(Overage,1,0) over (partition by pn order by rn) > 0
                                      then Allocation - lag(Overage,1,0) over (partition by pn order by rn)
                                      else Allocation
                                      end
                      end
            end as AdjustedAllocation
from d
order by pn
        ,rn;

---

Output

pn	rn	Material	Capacity	TotalAllocation	Allocation	AdjustedAllocation
1	1	abc	16	17	4	3
1	2	bac	16	17	9	9
1	3	cab	16	17	4	4
2	1	abc	12	17	4	0
2	2	bac	12	17	9	8
2	3	cab	12	17	4	4
3	1	abc	2	17	4	0
3	2	bac	2	17	9	0
3	3	cab	2	17	4	2
4	1	abc	112	47	14	14
4	2	bac	112	47	19	19
4	3	cab	112	47	14	14
5	1	abc	112	173	140	79
5	2	bac	112	173	19	19
5	3	cab	112	173	14	14