How to Subtract Depleting Values from Multiple SQL Rows Based on Consumption Order?

Subtract decreasing values ​​from rows based on consumption order in SQL

When dealing with a decreasing quantity scenario, where one table consumes quantity from another table, a way must be found to appropriately subtract the consumed value from the row. This question presents a situation where "Consumption Quantity" needs to be subtracted from multiple rows based on various conditions.

The task can be broken down into the following steps:

1. If not the last line:

   • Calculate "Quantity consumed from batch" as the difference between quantity and quantity consumed (if quantity consumed is less than quantity) or as the quantity itself.

2. If there are more rows:

   • Reduce the consumed quantity by the quantity consumed from the previous batch.

3. Repeat steps 1-2 until you reach the last row.

4. If it is the last line:

   • Set the quantity consumed from the batch to the remaining quantity consumed.

The SQL query provided in the answer efficiently completes these steps using Common Table Expressions (CTE). It first selects the first batch of each pool and calculates the run quantity and remaining demand based on the consumed quantity. It then uses a recursive union to add subsequent batches to each pool and update the run quantity and remaining demand.

Finally, it calculates the last line surplus or deficit for each pool by subtracting the run quantity from the remaining demand. The resulting set of rows and columns provides the desired output, where the consumed quantity is reduced from the merged batch in a row-by-row fashion.

The above is the detailed content of How to Subtract Depleting Values from Multiple SQL Rows Based on Consumption Order?. For more information, please follow other related articles on the PHP Chinese website!