Duplicate rows based on a column

Question

HI Team,

I am trying to duplicate the rows based on a column.

Input:

declare @table table ( 	accountid varchar(20), 	startdate date, 	numberofinstance int, 	instanceamount decimal(18,2) ) insert into @table select 1,'2023-09-10',3,100 union all select 2,'2023-10-20',4,101 union all select 3,'2023-09-20',2,100  select *from @table

• There is column which specify how many records (numberofinstance) to be created for an account
• Split the instanceamount among numberofinstance like 100/3 and the remaining needed to be adjusted to last instance
• Instance split is each for one month starting first instance as startdate

Expected ouptut:

--Expected output
select 1 accountid,'2023-09-10' instancedate,33.00 instanceamount
union all
select 1,'2023-10-10',33.00
union all
select 1,'2023-11-10',34.00--last instance is adjusted to match total amount
union all
select 2,'2023-10-20',25.00
union all
select 2,'2023-11-20',25.00
union all
select 2,'2023-12-20',25.00
union all
select 2,'2024-01-20',26.00--last instance is adjusted to match total amount and it went to next year
union all
select 3,'2023-09-20',50.00
union all
select 3,'2023-10-20',50.00

Thanks,

Eshwar.

Answer 1

Another query that is based on recursion:

;
with Q as
(
    select accountid, startdate, numberofinstance, instanceamount, 1 as i
    from @table
    union all
    select accountid, startdate, numberofinstance, instanceamount, i + 1
    from Q 
    where i < numberofinstance 
)
select accountid, dateadd(month, i - 1, startdate) as instancedate, 
    cast(
        case when i < numberofinstance then floor(instanceamount / numberofinstance)
        else instanceamount - floor(instanceamount / numberofinstance) * (numberofinstance - 1)
        end as decimal(18, 2)) as instanceamount
from Q
order by accountid, instancedate
option (maxrecursion 0)

In SQL Server 2022, try this:

select accountid, dateadd(month, s.value - 1, startdate) as instancedate, 
    cast(
        case when s.value < numberofinstance then floor(instanceamount / numberofinstance)
        else instanceamount - floor(instanceamount / numberofinstance) * (numberofinstance - 1)
        end as decimal(18, 2)) as instanceamount
from @table t
cross apply generate_series(1, t.numberofinstance) s
order by accountid, instancedate

Answer 2

This worked but any other solution?

WITH x AS  (   SELECT TOP (SELECT MAX(numberofinstance)+1 FROM @table) rn = ROW_NUMBER()    OVER (ORDER BY [object_id])    FROM sys.all_columns    ORDER BY [object_id] ) SELECT --* 	accountid,dateadd(m,rn-1,startdate) instancedate, 	case when (rn=numberofinstance and instanceamount%numberofinstance<>0) then 		round(instanceamount/numberofinstance,0) + round(instanceamount%numberofinstance,0) 	else round(instanceamount/numberofinstance,0) end instanceamount FROM x CROSS JOIN @table AS d WHERE x.rn <= d.numberofinstance ORDER BY accountid,dateadd(m,rn-1,startdate);

Thanks.