Subtract constant across database tables

Question

I need to subtract a value, found in a different table, from values across different rows. For example, the tables I have are:

ProductID | Warehouse | Locator | qtyOnHand
-------------------------------------------
100       | A         | 123     | 12
100       | A         | 124     | 12
100       | A         | 124     | 8
101       | A         | 126     | 6
101       | B         | 127     | 12

ProductID | Sold
----------------
100       | 26
101       | 16

Result:

ProductID | Warehouse | Locator | qtyOnHand | available
-------------------------------------------------------
100       | A         | 123     | 12        | 0
100       | A         | 123     | 12        | 0
100       | A         | 124     | 8         | 6
101       | A         | 126     | 6         | 0 
101       | B         | 127     | 12        | 12

The value should only be subtracted from those in warehouse A. Im using postgresql. Any help is much appreciated!

Answer 1

If I understand correctly, you want to compare the overall stock to the cumulative amounts in the first table. The rows in the first table appear to be ordered from largest to smallest. Note: This is an interpretation and not 100% consistent with the data in the question.

Use JOIN to bring the data together and then cumulative sums and arithmetic:

select t1.*,
       (case when running_qoh < t2.sold then 0
             when running_qoh - qtyOnHand < t2.sold then (running_qoh - t2.sold)
             else qtyOnHand
        end) as available
from (select t1.*,
             sum(qtyOnHand) over (partition by productID order by qtyOnHand desc) as running_qoh
      from table1 t1
     ) t1 join
     table2 t2
     using (ProductID)