While loop creating duplicate records

Question

I have a very simple query that takes two dates (start and end date, ex: 01/01/2018-01/31/2018) and then take out the week number of start and end date (ex: 1 and 5). I use while loop in order to enter all week numbers in one table (ex: 1,2,3,4,5). My while loop is creating duplicate records, like multiple records for all values. I am not sure what the issue is. Can someone suggest what I am might be doing wrong?

Code:

DECLARE @StartDate datetime = '01/01/2018'
    ,@EndDate datetime = '01/31/2018'
DECLARE @StartDateWeek INT  = DATEPART(WK,@StartDate)
    ,@EndDateWeek INT   = DATEPART(WK,@EndDate)
SELECT @StartDateWeek,@EndDateWeek

DECLARE @DateRangeWeekList TABLE(
Date DATE
,WeekNumber INT)

INSERT INTO @DateRangeWeekList
SELECT  Date = @StartDate
        ,WeekNumber = @StartDateWeek
select * from @DateRangeWeekList


WHILE (SELECT MAX(WeekNumber) FROM @DateRangeWeekList) < (SELECT     @EndDateWeek)
BEGIN
INSERT INTO @DateRangeWeekList
SELECT  Date = null
        ,WeekNumber = WeekNumber +1
FROM @DateRangeWeekList
END

select * from @DateRangeWeekList

Result:

Date        WeekNumber
2018-01-01  1
NULL        2
NULL        2
NULL        3
NULL        2
NULL        3
NULL        3
NULL        4
NULL        2 and more (total 18 records)

Expected Result

Date       WeekNumber
2018-01-01 1
NULL       2
NULL       3
NULL       4
NULL       5

Any time you write a loop in SQL, it ought to make you feel dirty and icky. It means you're probably doing something very wrong in the first place. — Joel Coehoorn
– Joel Coehoorn, Commented Feb 9, 2018 at 17:21
A tally table would be a much better approach than a loop for this. Here is a great article on the topic. sqlservercentral.com/articles/T-SQL/62867 — Sean Lange
– Sean Lange, Commented Feb 9, 2018 at 17:32

Thom A · Accepted Answer · 2018-02-09 17:07:54Z

3

A recursive CTE would be far better here than a WHILE loop:

DECLARE @StartDate datetime = '01/01/2018',
        @EndDate datetime = '01/31/2018';

WITH WeekNumbers AS(
    SELECT CONVERT(date,@StartDate) AS [Date],
           1 AS WeekNumber
    UNION ALL
    SELECT NULL,
           WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber <= DATEDIFF(WEEK, @StartDate, @EndDate))
SELECT *
FROM WeekNumbers;

You could also, however, consider using a Calendar table. There are lots of options out there for you.

answered Feb 9, 2018 at 17:07

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

5 Comments

Stephanie Over a year ago

Thanks for your answer. I am aware of CTE but my question was for duplicate records. Why dups in while loop I am using?

Thom A Over a year ago

@Stephanie Because you're selecting from the table every time and inserting the same results again. This creates a Cartesian product.

Sean Lange Over a year ago

Since we are trying to avoid looping it might be worth reading this. sqlservercentral.com/articles/T-SQL/74118 Still a good answer though. :)

Stephanie Over a year ago

I still don't understand, how I would be inserting same values when under insert statement I am selecting what is already inserted and adding a +1 to it

SMor Over a year ago

@Stephanie Because you select all rows in the table during each loop iteration. You start the loop with 1 row. 1st iteration adds 1 row for 2 total. 2nd iteration adds 2 rows for 4 total. And so on.

Josh · Accepted Answer · 2018-02-09 17:17:13Z

1

Perhaps you can try this format for inserting your values.

INSERT INTO @DateRangeWeekList (Date, WeekNumber)
VALUES (null, WeekNumber +1)

answered Feb 9, 2018 at 17:17

Josh

112 bronze badges

Collectives™ on Stack Overflow

While loop creating duplicate records

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related