У меня есть требование ограничить количество до определенного уровня. Приведенные ниже данные разбиты на разделы, а также указаны номера строк. Пожалуйста, ознакомьтесь с приведенными ниже текущими данными в таблице

Однако результат должен быть таким. Пожалуйста, обратитесь ниже.

Текущая вместимость грузовика составляет 16 кол-во. Но если вы видите, что общее количество выделенных ресурсов на уровне разделов равно 17. Поэтому нам нужно вычеркнуть этот дополнительный 1 Кол-во из конечного кол-во распределения динамически на уровне строк, предпочтительно с номером строки 1..2..3, как это. Я пытался выполнить полный цикл, используя цикл while, но безуспешно

То, что я пытался сделать до сих пор

 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
 

Вопреки тому, что было сказано в комментариях, это очень возможно с помощью оконных функций, просто это немного запутанно.

Предполагая, что я правильно понял вашу ситуацию, вы хотите удалить элементы из списка material в порядке их RowNumber следования до тех Truck capacity пор, пока они не будут достигнуты. Для этого просто требовались некоторые запущенные агрегаты и условные вычисления, основанные на значении в предыдущей строке:

Запрос

 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;
 

Выход