Let's say I have a table:

WITH MY_TABLE AS (SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 1 RN, 3500 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 2 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 3 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL)
SELECT * FROM MY_TABLE

I want to subtract PRODUCED according to TO_PRODUCE and leave the rest in the last RN (RowNumber) like

1000

1000

1500

I've written following query, but it works only if RN is not higher than 3. In last year's records, I've found that it was up to 32, but can be even higher.

SELECT ORDER_NUM, TO_PRODUCE, PRODUCT_ID, RN, PRODUCED, SUBTRACTING, 
CASE WHEN RN = 1 AND (PRODUCED <= TO_PRODUCE OR LEAD(RN) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) IS NULL) THEN PRODUCED
     WHEN RN = 1 AND PRODUCED > TO_PRODUCE THEN TO_PRODUCE
     WHEN LEAD(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) IS NULL AND SUBTRACTING > 0 THEN LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN)
     WHEN SUBTRACTING >= 0 AND LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) > 0 THEN TO_PRODUCE
     WHEN SUBTRACTING < 0 AND LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) > 0 THEN LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) 
     ELSE 0
     END AS TRUE_PRODUCED        
FROM (  
WITH MY_TABLE AS (SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 1 RN, 3500 PRODUCED, 3500 WPRODUCED FROM DUAL 
               UNION ALL
               SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 2 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL 
               UNION ALL
               SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 3 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL)
SELECT PRODUCT_ID, ORDER_NUM, TO_PRODUCE, PRODUCED, RN,
    CASE WHEN RN = 1 AND LEAD(RN) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) IS NULL THEN PRODUCED 
        ELSE CASE WHEN RN = 1 AND PRODUCED > TO_PRODUCE AND LEAD(RN) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) > 1 THEN PRODUCED - TO_PRODUCE
                        WHEN RN = 2 THEN LAG(PRODUCED - TO_PRODUCE) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) - TO_PRODUCE
                        WHEN RN = 3 THEN LAG(PRODUCED - TO_PRODUCE, 2) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) - LAG(TO_PRODUCE) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) - TO_PRODUCE
            END 
        END AS SUBTRACTING FROM MY_TABLE ORDER BY RN
);

This query gave me an output:

I want to make SUBTRACTING universal for any RN. I can proceed it with

WHEN RN = 4 THEN 
LAG(PRODUCED - TO_PRODUCE, 3) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) 
- LAG(TO_PRODUCE, 2) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN)
- LAG(TO_PRODUCE) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN)
- TO_PRODUCE

and continue similarly but I suppose there's a better (faster) way.

CodePudding user response：

You can do this by using the window function more effectively -

WITH MY_TABLE AS (SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 1 RN, 3500 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 2 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 3 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL)
SELECT ORDER_NUM, TO_PRODUCE, PRODUCT_ID, RN, PRODUCED,
       SUM(PRODUCED) OVER() - 
       SUM(TO_PRODUCE) OVER (ORDER BY RN) SUBTRACTING
  FROM MY_TABLE;

Demo.