SQL Splitting Rows Into Multiple Rows Based on Condition

Admin 1
Viewed 43

I have the below table:

Account Credit Debt Net ItemCount InvoiceNumber
AAA 1300 150 1150 2 10
AAA 500 50 450 12 20
AAA 1800 650 1150 29 30

No record can have a higher count than 10 items so anything over 10 must be split into multiples of 10 until the last remaining value (which could be less than 10)

I am trying to automate this in SQL and have not been able to come up with an idea that works, I would like something to look at the above table and spit out the following:

Account Credit Debt Net ItemCount InvoiceNumber
AAA 1300 150 1150 2 10
AAA 500 50 450 10 20
AAA 500 50 450 2 30
AAA 1800 650 1150 10 10
AAA 1800 650 1150 10 20
AAA 1800 650 1150 9 30

Any thoughts on how can this be accomplished?

sql
2 Answers

If you work with SQL Server, this should do the trick for you

CREATE TABLE #Table1
(
    [Account] varchar(3), 
    [Credit] int, 
    [Debt] int, 
    [Net] int, 
    [ItemCount] int,
    [InvoiceNumber] int
);
    
INSERT INTO #Table1 ([Account], [Credit], [Debt], [Net], [ItemCount], [InvoiceNumber])
VALUES
    ('AAA', 1300, 150, 1150, 2, 10),
    ('AAA', 500, 50, 450, 12, 20),
    ('AAA', 1800, 650, 1150, 29, 30)
;

WITH cte_numbers([Account] , [Credit] , [Debt] , [Net] , [ItemCount] , [InvoiceNumber] , itemcountzero, currentitmecount) 
AS 
(
    SELECT 
        *, 
        (itemcount % 10) AS itemcountzero, 
        (itemcount) AS currentitmecount
    FROM
        #Table1
    UNION ALL
    SELECT    
        t.[Account] , t.[Credit] , t.[Debt] , t.[Net] , t.[ItemCount] , t.[InvoiceNumber] , c.itemcountzero, (c.currentitmecount-10)
    FROM  
        #Table1 t
    INNER JOIN
        cte_numbers c ON t.[InvoiceNumber] = c.[InvoiceNumber] 
                      AND (c.currentitmecount-10) >= c.itemcountzero 
                      -- AND (currentitmecount-10) > 0
)
SELECT 
    [Account] , [Credit] , [Debt] , [Net] , IIF(currentitmecount > 10, 10 , currentitmecount) as [ItemCount] , [InvoiceNumber]
FROM 
    cte_numbers
ORDER BY
    [InvoiceNumber], currentitmecount DESC;
user_22187130

I would try to use a cursor to iterate through the records and generate as many inserts as you need based on the value that you have

user_6929750
Related