Repeating rows but changing column value each time

I’ve this table with the following data: ID  Quantity Status 1    250      OK   2    440      HOLD 3    180      HOLD 4    860      OK

Based on the value in the Quantity column for each row, the row should be repeated again if Quantity is bigger than 200, but repeated with the quantity remaining and each row capped with the value 200.

For example, for ID 1, the Quantity value is 250 so the ID 1 should appear the first time with 200 and the second time with 50.

The resultant table should be as below: ID  Quantity Status 1    200      OK   1    50      OK   2    200      HOLD 2    200      HOLD 2    40      HOLD 3    180      HOLD 4    200      OK   4    200      OK   4    200      OK   4    200      OK   4    60      OK  

Can someone please help me out with this query? am using SQL SERVER.

Answer

I think your best bet here is some recursive CTE:

WITH RECURSIVE quantitySpreader AS
(
    /*Recursive Seed (starting point)*/
    SELECT 
        ID, 
        CASE WHEN Quantity >= 200 then 200 ELSE Quantity END as Quantity,
        Status, 
        1 as Depth,
        CASE WHEN test.Quantity >= 200 THEN test.Quantity - 200 ELSE 0 END as remainder
    FROM test

    UNION ALL

    /*Recursive member (sql that iterates until join fails)*/
    SELECT
        quantitySpreader.ID,
        CASE WHEN remainder >= 200 THEN 200 ELSE remainder END,
        quantitySpreader.Status,
        depth + 1,
        Case when remainder >= 200 THEN remainder - 200 else 0 END
    FROM
        quantitySpreader
        INNER JOIN test 
            ON quantitySpreader.ID = test.ID
            AND quantitySpreader.Quantity >= 200
    WHERE depth <= 10
)
SELECT id, quantity, status 
FROM quantitySpreader
ORDER BY id, quantity DESC;

This can get a little heady, but Recursive sql like this is split into two chunks inside that CTE.

1. The recursive starting point/seed. This defines the starting point for iterating. Here we want every record (so no WHERE clause is present) and we establish the first iteration. We want “200” unless the quantity is less than 200, then just the quantity. We are also tracking the depth of recursiveness (to keep us from cycling endlessly) as well as the remainder after we subtract that 200.
2. After the UNION ALL is the recursive member. This SELECT statement will repeat over and over and over again referring to its own result set (quantitySpread) until the JOIN fails and returns nothing. Each iteration we do the same logic as above. Check if the quantity is over 200, and if so, set the output to 200 and recalculate the remainder for the next iteration.

SQLFiddle of this in action It’s running on Postgres, but the syntax is nearly identical for SQL Server so it should just be a copy/paste job.

Input:

CREATE TABLE test (id int, Quantity int, Status varchar(10));
INSERT INTO test VALUES (1, 250, 'OK');
INSERT INTO test VALUES (2, 440, 'HOLD');

Output:

id	quantity	status
1	200	OK
1	50	OK
2	200	HOLD
2	200	HOLD
2	40	HOLD