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

Now, based on the given date, we need to use the total inventory to deduce the initial date, which is the day when there is zero or negative inventory. We need to add the daily consumption of UPDATED_QTY and the original inventory UPDATED_CUSTQTY. 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.

SQL：

SELECT t.*,
       LEAST(
         GREATEST(
           COALESCE(
             SUM(
               CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
             ) OVER (PARTITION BY item, loc ORDER BY needdate DESC) - custqty,
             qty
           ),
           0
         ),
         qty
       ) AS updated_qty,
       CASE
       WHEN needdate > TRUNC(to_date('2024-02-26'))
       THEN NULL
       WHEN SUM(
              CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
            ) OVER (
              PARTITION BY item, loc
              ORDER BY needdate DESC
              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) >= custqty
       THEN NULL
       ELSE GREATEST(
              custqty
              - SUM(
                CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
              ) OVER (PARTITION BY item, loc ORDER BY needdate DESC),
              0
           )
       END AS updated_custqty
FROM   table_name t

SQL requires multiple window functions to indirectly implement ordered calculation, and the code is complex and difficult to understand. SPL can directly represent relative or absolute position:

https://try.esproc.com/splx?3FZ

A1： Load data, sort in reverse order by date, and add two calculated columns: the consumed inventory UPDATED_QTY, with the initial value being the inbound quantity; The original inventory UPDATED_CUSTQTY has an initial value of null.

A2： Filter out records before the specified date.

A3： Modify the record: If the current record is the first one, then original inventory=total inventory - received quantity; otherwise, original inventory=previous original inventory - received quantity, with the result rounded to one decimal place. If the original inventory is greater than or equal to 0, then the received quantity after consumption is 0. [-1] represents the previous record.

Question source：https://stackoverflow.com/questions/78060568/why-i-can-not-use-lag-function-in-this-case