title: SQL query CTE or while for case to split lines with correct quantity
body: <p>We have an issue that we don’t know how to solve, help is very welcome or an example how to fix such cases.</p>
  <p>This concerns purchase needs that have been combined into one purchase line. On receipt, the lines can be split due to multiple batch numbers. Below we have described the case. should there be a recursive solution (cte) for this or a while?</p>
  <p>Needs that are combined into one purchase line</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>purchase_need_id</th>
  <th>quantity</th>
  <th>item_id</th>
  <th>warehouse_id</th>
  <th>purchase_order_id</th>
  <th>purchase_order_line_item_id</th>
  <th>date_needed</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>5303</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>07/08/2021</td>
  </tr>
  <tr>
  <td>5304</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>23/07/2021</td>
  </tr>
  <tr>
  <td>5344</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>09/07/2021</td>
  </tr>
  <tr>
  <td>5345</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>23/08/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The goods receipt</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>goods_receipt_id</th>
  <th>goods_receipt_line_item_id</th>
  <th>purchase_order_id</th>
  <th>purchase_order_line_item_id</th>
  <th>item_id</th>
  <th>quantity</th>
  <th>lot_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>25</td>
  <td>84</td>
  <td>240</td>
  <td>1435</td>
  <td>114159</td>
  <td>120.00</td>
  <td>654654</td>
  </tr>
  <tr>
  <td>25</td>
  <td>85</td>
  <td>240</td>
  <td>1435</td>
  <td>114159</td>
  <td>180.00</td>
  <td>654445</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>a result like below is required, sorted bij date_needed</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>purchase_need_id</th>
  <th>quantity</th>
  <th>item_id</th>
  <th>warehouse_id</th>
  <th>purchase_order_id</th>
  <th>purchase_order_line_item_id</th>
  <th>date_needed</th>
  <th>goods_reciept_id</th>
  <th>goods_receipt_line_item_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>5344</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>09/07/2021</td>
  <td>25</td>
  <td>84</td>
  </tr>
  <tr>
  <td>5304</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>23/07/2021</td>
  <td>25</td>
  <td>85</td>
  </tr>
  <tr>
  <td>5303</td>
  <td>60.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>07/08/2021</td>
  <td>25</td>
  <td>85</td>
  </tr>
  <tr>
  <td>5303</td>
  <td>60.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>07/08/2021</td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>5345</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>23/08/2021</td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The purchase needs updated / inserted ordered by need_date</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>goods_receipt_id</th>
  <th>goods_receipt_line_item_id</th>
  <th>purchase_order_id</th>
  <th>purchase_order_line_item_id</th>
  <th>item_id</th>
  <th>quantity</th>
  <th>lot_id</th>
  <th>linked</th>
  <th>purchase_need_id</th>
  <th>quantity</th>
  <th>item_id</th>
  <th>warehouse_id</th>
  <th>purchase_order_id</th>
  <th>purchase_order_line_item_id</th>
  <th>date_needed</th>
  <th>goods_reciept_id</th>
  <th>goods_receipt_line_item_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>25</td>
  <td>84</td>
  <td>240</td>
  <td>1435</td>
  <td>114159</td>
  <td>120.00</td>
  <td>654654</td>
  <td>--&gt;</td>
  <td>5344</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>09/07/2021</td>
  <td>25</td>
  <td>84</td>
  </tr>
  <tr>
  <td>25</td>
  <td>85</td>
  <td>240</td>
  <td>1435</td>
  <td>114159</td>
  <td>180.00</td>
  <td>654445</td>
  <td>--&gt;</td>
  <td>5304</td>
  <td>120.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>23/07/2021</td>
  <td>25</td>
  <td>85</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td>--&gt;</td>
  <td>5303</td>
  <td>60.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>07/08/2021</td>
  <td>25</td>
  <td>85</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td>remaining quantity</td>
  <td>5303</td>
  <td>60.00</td>
  <td>114159</td>
  <td>8</td>
  <td>240</td>
  <td>1435</td>
  <td>07/08/2021</td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>