How to Deduce the Initial Date from the Total Value with esProc

A certain database table records the planned inbound quantity and inventory after inbound on a specific date, such as the planned inbound quantity of 0.6 on February 26th, resulting in a total inventory of 3.
source table

Now, based on the given date, we need to use the total inventory and the planned inbound inventory to deduce the initial date, which is the day when there is zero or negative inventory. We need to add the daily inventory before inbound as UPDATED_CUSTQTY and return the complete data. For example, given February 26th, it can be known that the original inventory of the day before inbound was 3-0.6=2.4; The previous date was February 23rd, and the original inventory on that day was 2.4-0.6=1.8; Until February 20th, the original inventory for that day was 0.
expected results

SQL lacks natural sequence numbers and convenient syntax to express relative positions, requiring the indirect implementation of multiple window functions, making it difficult to write code. SPL can directly represent relative or absolute position. 👉🏻 Try.DEMO
esProc SPL code

A1： Load data, sort in reverse order by date, add a calculated column for inventory before inbound UPDATED_CUSTQTY, with an initial value of null.

A2： Filter out records before the specified date that reference A1's records, and modifying these records will affect A1.

A3： Loop modifying each record in A2: If it is currently the first record, then original inventory=total inventory - received quantity, otherwise it is equal to "previous original inventory - received quantity", until this value is less than 0, and the result is rounded to one decimal place. [-1] represents the previous record. The modified A2 is as follows:

A4： Return the complete data A1, where the red box represents A2.

🚀✨ Get Started with esProc SPL – esProc SPL FREE Download.