title: Get next available date for an out-of-stock, but incoming product
body: <p>I am running an online store that gets all of it's stock info from an external ERP system. I have a few products that are on backorder, and I want to show the next available date on the product page.</p>
  <p>I have access to the ERP system database and all incoming shipment delivery dates, deliveries for a single product would look like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>product</th>
  <th>incoming_date</th>
  <th>incoming</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ABC123</td>
  <td>20210607</td>
  <td>34</td>
  </tr>
  <tr>
  <td>ABC123</td>
  <td>20210621</td>
  <td>17</td>
  </tr>
  <tr>
  <td>ABC123</td>
  <td>20210705</td>
  <td>34</td>
  </tr>
  <tr>
  <td>ABC123</td>
  <td>20210715</td>
  <td>17</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I also know that for this spesific product I have 59 units on backorder from customers (number is available in the same database, I have control over this and therefore it's just stored as the variable @Backorder (integer) in the following query). I currently have 0 in stock. This means that the first and second shipment is already sold, and the next available date would be 2021-07-05 (third row).</p>
  <p>I've been fiddling with this the last couple of hours, but I am unable to find a good solution.</p>
  <p>The closest I have come is this:</p>
  <pre><code>DECLARE @Product AS VARCHAR(100)='ABC123';
  DECLARE @Sold AS INT = 59;
  
  SELECT row, product, incoming_date, incoming, available,
  
  CASE WHEN available &lt; 0 THEN 0 ELSE available-LAG(ABS(available),1) OVER (ORDER BY row) END AS new_available
  
  FROM 
  (SELECT row, product, incoming_date, incoming,
      CASE WHEN available &lt; 0 THEN available ELSE incoming-ABS(LAG(available) OVER (ORDER BY row)) END AS available
  
      FROM (
          SELECT ROW_NUMBER() OVER(ORDER BY ArDt ASC) AS row, ProdNo AS product, ArDt AS incoming_date, SUM(NoInvoAb) AS incoming,
          CASE WHEN ROW_NUMBER() OVER(ORDER BY ArDt ASC) = 1 
          THEN SUM(NoInvoAb)-@Sold ELSE 0 END AS available
          FROM OrderLines WHERE TrTp = 6 AND NoInvoAb != 0 AND ProdNo = @Product GROUP BY ArDt, ProdNo
      ) AS A
  ) AS B
  </code></pre>
  <p>Which results in this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>row</th>
  <th>product</th>
  <th>incoming_date</th>
  <th>incoming</th>
  <th>available</th>
  <th>new_available</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>ABC123</td>
  <td>20210607</td>
  <td>34.000000</td>
  <td>-25.000000</td>
  <td>0.000000</td>
  </tr>
  <tr>
  <td>2</td>
  <td>ABC123</td>
  <td>20210621</td>
  <td>17.000000</td>
  <td>-8.000000</td>
  <td>0.000000</td>
  </tr>
  <tr>
  <td>3</td>
  <td>ABC123</td>
  <td>20210705</td>
  <td>34.000000</td>
  <td>34.000000</td>
  <td>26.000000</td>
  </tr>
  <tr>
  <td>4</td>
  <td>ABC123</td>
  <td>20210715</td>
  <td>17.000000</td>
  <td>17.000000</td>
  <td>-17.000000</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>As you can see, it kinda works, but the fourth and following rows it stops giving correct results. I would have to loop the &quot;new_available&quot; over and over, which is... not desirable.</p>
  <p>Complete result shown for debug purposes. In the end I don't need all the information in the table, just the next available date, ie. 2021-07-05 where there are 26 units available.</p>
  <p>Are there any better solutions to this problem? Any ideas on a better approach?</p>
  <p>PS: Must be solved in the SQL-query itself, calculating it in the webshop is (for now) not an option.</p>
  <p>All responses and ideas appreciated!</p>