TSQL Loop through table records and compare against 2nd table

Question

I have two temp tables, both populated by TSQL queries.

Temp Table A containing a Location, an Item, and a total inventory count.

Temp Table B containing a Location, an Item, a Sales Order Number, and negative inventory count.

I would like to loop through each item in Table B and subtract the negative inventory count from table A where the location and item match. Once the inventory reaches <=0 in Table B, I want to output each order into a 3rd table which holds the Location, Item, and Sales Order Number.

EXAMPLE TABLE A

Item|Location|Inventory
1    A        10
2    B        20

EXAMPLE TABLE B

Order|Item|Location|QuanityUsed
ABC  |1   |A       |5
ZYX  |2   |B       |10
DEF  |1   |A       |6

DEF would be output into TABLE C because there is not enough inventory to fill the order after subtracting order ABC.

How can I accomplish this?

Answer 1

You could use aubquery to calculate a running total. In the outer query, you could specify that the running total must be greater than the total inventory:

select  location
,       item
,       [Order]
,       TotalInventory
,       RunningTotal
from    (
        select  [order].location
        ,       [order].item
        ,       [order].[Order]
        ,       (
                select  SUM(inventory)
                from    @a inv
                where   inv.item = [order].item
                        and inv.location = [order].location
                ) as TotalInventory
        ,       (
                select  SUM(QuantityUsed)
                from    @b prevorder
                where   prevorder.item = [order].item
                        and prevorder.location = [order].location
                        and prevorder.[order] <= [order].[order]
                ) as RunningTotal
        from    @b [order]
        ) as OrderExtended
where   TotalInventory < RunningTotal

Test data:

declare @a table (item int, location char(1), inventory int)
insert into @a select 1, 'A', 10
union all select 2, 'B', 20
declare @b table ([order] char(3), item int, location char(1), QuantityUsed int)
insert into @b select 'ABC', 1, 'A', 5
union all select 'ZYX', 2, 'B', 10
union all select 'DEF', 1, 'A', 6

This prints:

location  item  Order  TotalInventory  RunningTotal
A         1     DEF    10              11

So order DEF causes location A to exceed its item 1 inventory by 10 - 11 = 1.