SQL Server 2012 - Reset Running Total Based on the Value of Another Column

user_1205770 0
Viewed 95

I'm using SQL Server 2012 to try and set new forecast targets when the weekly quantity value meets or exceeds the minimum lot quantity. I know this will require a running total and also a way to flag what row the running total will need to be reset on.

Currently trying a recursive solution, but I would be fine with a windowed approach as well. Thanks!

CREATE TABLE dbo.Table_1
(
    ITEM_ID nvarchar(20) NOT NULL,
    DUE_DATE datetime NOT NULL,
    MIN_LOT_SIZE int NOT NULL,
    QUANTITY int NOT NULL
)

INSERT INTO dbo.Table_1 (ITEM_ID, DUE_DATE, MIN_LOT_SIZE, QUANTITY) 
VALUES
('ITEM_1', '1/4/2021', 460, 102)
,('ITEM_1', '1/11/2021', 460, 101)
,('ITEM_1', '2/8/2021', 460, 100)
,('ITEM_1', '3/8/2021', 460, 101)
,('ITEM_1', '4/5/2021', 460, 141)
,('ITEM_1', '5/10/2021', 460, 142)
,('ITEM_1', '6/7/2021', 460, 142)
,('ITEM_1', '7/5/2021', 460, 142)
,('ITEM_1', '8/2/2021', 460, 142)
,('ITEM_1', '9/6/2021', 460, 142)
,('ITEM_1', '10/4/2021', 460, 142)
,('ITEM_1', '11/1/2021', 460, 142)
,('ITEM_2', '10/4/2021', 5000, 1057)
,('ITEM_2', '11/1/2021', 5000, 1422)
,('ITEM_3', '1/4/2021', 3050, 17)
,('ITEM_3', '1/11/2021', 3050, 761)
,('ITEM_3', '2/1/2021', 3050, 752)
,('ITEM_3', '3/1/2021', 3050, 760)
,('ITEM_3', '4/5/2021', 3050, 1059)
,('ITEM_3', '5/3/2021', 3050, 1066)
,('ITEM_3', '6/7/2021', 3050, 1066)
,('ITEM_3', '7/5/2021', 3050, 1066)
,('ITEM_3', '8/2/2021', 3050, 1061)
,('ITEM_3', '9/6/2021', 3050, 1066)
,('ITEM_3', '10/4/2021', 3050, 1061)
,('ITEM_3', '11/1/2021', 3050, 1066)

My current code below works for ITEM_1 with regards to flagging the correct row, but the subsequent ITEM_1 running total goes into ITEM_2 after the second flag and will be the case going forward for all items.

Query:

WITH cte AS 
(
    SELECT 
        rn, item_id, due_date, min_lot_size, quantity, running_total, flag 
    FROM 
        (SELECT 
             ROW_NUMBER() OVER (ORDER BY item_id, due_date) AS rn,
             item_id,
             due_date,
             min_lot_size,
             quantity,
             SUM(quantity) OVER (PARTITION BY item_id ORDER BY due_date ASC, quantity DESC ROWS UNBOUNDED PRECEDING) AS running_total,
             CASE 
                WHEN SUM(quantity) OVER (PARTITION BY item_id ORDER BY due_date ASC, quantity DESC ROWS UNBOUNDED PRECEDING) >= min_lot_size 
                   THEN 1 
                   ELSE 0 
             END AS flag
         FROM 
             Table_1) A
     WHERE 
         rn = 1
     UNION ALL
     SELECT 
         r.rn, r.item_id, r.due_date, r.min_lot_size, r.quantity,
         CASE c.flag
            WHEN 1 THEN r.quantity
            ELSE c.running_total + r.quantity
         END,
         CASE 
            WHEN 
               CASE c.flag
                  WHEN 1 THEN r.quantity
                  ELSE c.running_total + r.quantity
               END > c.min_lot_size 
               THEN 1 ELSE 0 
         END
     FROM   
         cte c
     JOIN 
         (SELECT 
              ROW_NUMBER() OVER (ORDER BY item_id, due_date) AS rn, 
              item_id, due_date, min_lot_size, quantity 
          FROM Table_1) r ON r.rn = c.rn + 1
)
SELECT *
FROM cte 
ORDER BY rn
OPTION (maxrecursion 0)

Desired output:

Desired Output:

cumulative-sum recursive-query sql-server-2012 tsql sql
1 Answers

With a problem like this, you have to get really meta about the running result (the running result for a row depends not on the previous value but rather on the previous running result). I can't think of a way to do this inside a CTE when SQL Server handles window functions in a way that differs and is less intuitive compared to other implementations such as PostgreSql. It may even be considered buggy if there is a standard that it is violating.

The strategy I give below uses recursion with a while loop. But it's not RBAR so I don't think performance will be horrible compared to what you might fear with such a loop. In fact, I structured it to be analogous to the syntax of a recursive CTE, if windowed functions worked inside the recursive part in SQL Server. Incidentally, I actually implemented it as a recursive CTE in postgreSQL with success.

One thing to keep in mind for the loop is that I make your 'flag' a little more complex. I call my flag 'processed', and it can take on three values: 1, -1, and 0. 0 means 'not processed', 1 means 'processed', and -1 is also processed but a special marker that pretty much serves as your 'flag'.

-- The Anchor Part
select  *, 
        result = 0,
        processed = 0
into    #results
from    Table_1

-- The Recursive Part
while exists (select 0 from #results where processed = 0)

    update  r
    set     r.result = r.runSum,
            r.processed = 
                case 
                when r.runSum <= r.min_lot_size then 1
                -- first entry that is greater than min_lot_size
                when r.runSum - quantity < r.min_lot_size then -1 
                else 0
                end
    from    (
                select  *,
                        runSum = sum(quantity) over(
                            partition by item_id 
                            order by due_date
                        )
                from    #results
                where   processed = 0
            ) r;

-- Final Output
select  ITEM_ID, 
        DUE_DATE, 
        MIN_LOT_SIZE, 
        QUANTITY, 
        NEW_QUANTITY = iif(processed = -1, result, 0)
from    #results;
  • Here it is with runnable results in SQL Server: sql server.
  • Here it is as a true recursive CTE as I discuss above: postgre.
user_34165510
Related