SQL take total from several rows until used up

Question

In the below example the item code is split into 4 different lots (different versions of the same product) the Item has 780 items allocated to orders across all lots. however the first lot only has 207 available i need another column to work out how many units are available using the oldest lot first eg the first lot in the example would be used up so would the 2nd and 3rd and there would be 382 units available from the final lot. im not too sure how to write this in sql. There are many more products in the dataset some with more and some with less lots.

Any Help would be apricated

Select 
s.[Item Code]
,s.Lot
,s.[Allocated to Orders]
,s.[Available QOH]

from #StockValuation1 s
where s.[Item Code] = 'Test12080'

Desired outcome -

Answer 1

My solution utilises a window function to capture the running total as at the current allocation, which is then used to calculate the allocation per Lot and the remaining:

declare @t table(ItemCode int,Lot int,Allocated int, Available int);
insert into @t values
 (1,1,780,207)
,(1,2,780,400)
,(1,3,780,55)
,(1,4,780,500)
,(1,5,780,100)

,(2,1,430,270)
,(2,2,430,140)
,(2,3,430,150)
,(2,4,430,50)
,(2,5,430,100)
;

with rt as
(
    select ItemCode
          ,Lot
          ,Allocated
          ,Available
          ,case when rt >= Allocated
                then Allocated - (rt - Available)
                else rt - (rt - Available)
                end as LotAllocation
    from (select *
                ,sum(Available) over (partition by ItemCode order by Lot) as rt
          from @t
         ) as t
)
select ItemCode
      ,Lot
      ,Allocated
      ,Available
      ,case when LotAllocation < 0
            then 0
            else LotAllocation
            end as LotAllocation
      ,case when LotAllocation < 0
            then Available
            else Available - LotAllocation
            end as AvailableLessAllocation
from rt
order by ItemCode
        ,Lot;

---

Output:

+----------+-----+-----------+-----------+---------------+-------------------------+
| ItemCode | Lot | Allocated | Available | LotAllocation | AvailableLessAllocation |
+----------+-----+-----------+-----------+---------------+-------------------------+
|        1 |   1 |       780 |       207 |           207 |                       0 |
|        1 |   2 |       780 |       400 |           400 |                       0 |
|        1 |   3 |       780 |        55 |            55 |                       0 |
|        1 |   4 |       780 |       500 |           118 |                     382 |
|        1 |   5 |       780 |       100 |             0 |                     100 |
|        2 |   1 |       430 |       270 |           270 |                       0 |
|        2 |   2 |       430 |       140 |           140 |                       0 |
|        2 |   3 |       430 |       150 |            20 |                     130 |
|        2 |   4 |       430 |        50 |             0 |                      50 |
|        2 |   5 |       430 |       100 |             0 |                     100 |
+----------+-----+-----------+-----------+---------------+-------------------------+

Answer 2

You can use cumulative sums. First get the amount allocated to the orders:

select s.*,
       (case when allocated_to_orders >= running_qoh
             then available_qoh
             when allocated_to_orders <= running_qoh - available_qoh
             then running_qoh - allocated_to_orders
             else 0
       end) as used_in_orders
from (select s.*,
             sum(available_qoh) over (partition by item_code order by lot) as running_qoh
      from #StockValuation1 s
     ) s
where s.[Item Code] = 'Test12080';

Then use a subquery or CTE to get the difference:

select s.*,
       (available_qoh - used_in_orders) as available_for_orders
from (select s.*,
             (case when allocated_to_orders >= running_qoh
                   then available_qoh
                   when allocated_to_orders <= running_qoh - available_qoh
                   then running_qoh - allocated_to_orders
                   else 0
             end) as used_in_orders
      from (select s.*,
                   sum(available_qoh) over (partition by item_code order by lot) as running_qoh
            from #StockValuation1 s
           ) s
      where s.[Item Code] = 'Test12080'
     ) s;

Note: I strongly recommend that you stop using spaces in your column names so you don't have escape them. The escape characters just make queries harder to write, read, and maintain.