I have the below table:

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:

Any thoughts on how can this be accomplished?

CodePudding user response：

if you work with mssql 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
    ;

CodePudding user response：

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