title: Reduce field value based on previous value on row
body: <p>I have a table like this:</p>
  
  <pre><code>Order Number  ItemCode   Quantity_Ordered   AvailableInShop
  -----------------------------------------------------------
  1             10         3                  3
  2             10         2                  3 
  </code></pre>
  
  <p>but I need a query to get this output:</p>
  
  <pre><code>Order Number  ItemCode   Quantity_Ordered   AvailableInShop
  -----------------------------------------------------------
  1             10         3                  3
  2             10         2                  0
  </code></pre>
  
  <p>This is because the first sales order has taken all the quantity for subsequent ones.</p>
  
  <p>Here is what i tried</p>
  
  <pre><code>select 
    [order number], 
    itemcode, 
    quantity_ordered, 
    availableInshop, 
    Row_Number() over (partition itemcode order by [order number] asc) Rownumber  
  from Orders
  </code></pre>