Cumulative subtraction across rows

3344 views sql
2

Table 1:
table1:

Table 2:
enter image description here

How can I subtract the Committed value (7) from the IncomingQuantity cumulatively across rows? So that the result would look like:
enter image description here

Thanks!

answered question

1 Answer

7

You need a cumulative sum and some arithmetic:

select t.*,
       (case when running_iq - incomingquantity >= committed then 0
             when running_iq > committed then running_iq - committed
             else 0
        end) as from_this_row
from (select t2.*, t1.committed,
             sum(incomingquantity) over (order by rowid) as running_iq
      from table1 t1 cross join
           table2 t2
     ) t;

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories