Nested WHILE Loop with INSERT produced duplicate records

Question

I'm looking for you, SQL Experts (or maybe this is a quite easy problem, than of course everyone is welcome :) )

I start with the following table:

ID|Contract Start Date|Runtime (Months)|MonthCount
1 |2015-04-01         |48              |1
2 |2018-02-01         |36              |1

I want to create a table which creates (inserts) new rows based on the recoreds in the column Contract Start Date and Runtime. In other words I need to create a row for each month starting from the startdate unitl the number of runtime is reached.

ID|Contract Start Date|Runtime (Months)|MonthCount
1 |2015-04-01         |48              |1
1 |2015-05-01         |48              |1
1 |2019-04-01         |48              |48
2 |2018-02-01         |36              |1
2 |2018-03-01         |36              |2
2 |2021-03-01         |36              |36

I tried achiving this with nested cursor and while loops, this works somehow; 'the only issue' is, that I create a cartesian product/duplicate records.

While researching I found this: While loop creating duplicate records; but I'm not shure how to apply this to my code

SQL:

DECLARE @ID INT
DECLARE @ContractStartDate DATE
DECLARE @ContractRuntime INT
DECLARE @MonthCount INT




 --Declaring Cursor
 DECLARE MonthCursor CURSOR 
    FOR SELECT * FROM LeasingData

OPEN MonthCursor


    FETCH NEXT FROM MonthCursor
        INTO @ID,@ContractStartDate, @ContractRuntime, @MonthCount

    WHILE @@FETCH_STATUS = 0
        BEGIN 

        WHILE @MonthCount < @ContractRuntime
            BEGIN
            SET @MonthCount = @MonthCount + 1
            SET @ContractStartDate = DATEADD(month, 1, @ContractStartDate)

            INSERT INTO Table
            SELECT @ID,@ContractStartDate, @ContractRuntime, @MonthCount

            END

        FETCH NEXT FROM MonthCursor
            INTO @ID,@ContractStartDate, @ContractRuntime, @MonthCount

        END

CLOSE MonthCursor
DEALLOCATE MonthCursor

This results in:

ID|Contract Start Date|Runtime (Months)|MonthCount
1 |2015-04-01         |48              |1
1 |2015-05-01         |48              |2
1 |2015-06-01         |48              |3
1 |2015-06-01         |48              |3
1 |2015-06-01         |48              |3
1 |2015-06-01         |48              |3
1 |2015-07-01         |48              |4
1 |2015-07-01         |48              |4
1 |2015-07-01         |48              |4
1 |2015-07-01         |48              |4
1 |2015-07-01         |48              |4
1 |2015-07-01         |48              |4

and so on....

Maybe I'm completly on the wrong path with my solution. Glad to learn something new :)

A WHILE loop is most definitely the worst way to do this, I'm afraid. A Tally table would be a far better option. — Thom A
– Thom A ♦, Commented Aug 7, 2019 at 15:11

Gordon Linoff · Accepted Answer · 2019-08-07 15:12:22Z

1

Use a recursive CTE:

with cte as (
      select id, contract_start_date, runtime, monthcount
      from t
      union all
      select id, dateadd(month, monthcount, contract_start_date), runtime - 1, monthcount
      from cte
      where runtime > 0
    )
select *
from cte
order by id, contract_start_date;

If you need more than 100 months, add option (maxrecursion 0).

answered Aug 7, 2019 at 15:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

malatok Over a year ago

Hi @gordon-linoff, thank you for pointing me in this direction. Will try this!

Collectives™ on Stack Overflow

Nested WHILE Loop with INSERT produced duplicate records

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related