SQLTeam.com | Weblogs | Forums

Query Quantity Calculation

I need some big time help with this problem.

Table A
Item, Onhand, Allocated

Table B
Item, PO Date, Order Quantity

This Example Works - Sample Data
(The onhand - committed > 0) this works fine
Item Onhand Committed PO Date PO Qty
ABC1 100 10
ABC1 4/1/2022 20
ABC1 4/8/2022 30
ABC1 4/14/2022 45

Required Results - and this is good
Export Data Qty Date Notes
ABC1 90 Today (100 - 10) = 90 - Availabile today
ABC1 20 4/1/2022 20 - entire amount in the output file
ABC1 30 4/8/2022 30 - entire amount in the output file
ABC1 45 4/14/2022 45 - entire amount in the output file

This Example Does Not Work
(The onhand - committed < 0) does not work
|Item Onhand Committed Date PO Qty
DEF 100 250 Today
DEF1 4/1/2022 100
DEF1 4/8/2022 150
DEF1 4/14/2022 400

Required Results
Item Qty Date Notes
DEF1 100 4/8/2022 (100 - 250 + 100 + 150)
DEF1 400 4/14/2022 400 since the available > 0 at this point

please provide sample data as follows

declare @incense table(id int, name varchar(50))

insert into @incense
select 1, 'Frank'