Why does my week of the year's query show "2022.52" when it should show "2021.52"

Question

I have a query that gave me all the 12 following weeks :

      insert #T_period_futur
               (TPF_year,
                TPF_period_number,
                TPF_start_period,
                TPF_end_period
               )
         select year(dateadd(month, @period_number * +1-1, @next_end_month)),
          convert(varchar(4), case when datepart(ISO_WEEK, dateadd(week, @period_number * +1, @next_sunday)) < 53
                                      then year(dateadd(week, @period_number * +1, @next_sunday))
                                      else year(dateadd(week, @period_number * +1, @next_sunday)) - 1
                                    end) 
                + '.' + right('00' + convert(varchar(4), datepart(ISO_WEEK, dateadd(week, @period_number -1, @next_sunday))), 2),
               dateadd(week, @period_number * +1, dateadd(day, (datepart(weekday, @today)-1) * -1 - 7+1, @today)),
             dateadd(week, @period_number -1, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6))
               ;

I call it inside a while loop that does this :

while @period_number <= @nb_period
begin 

 --The previous query..
   select @period_number = @period_number + 1;

end;

With @period_number = 1 and @nb_period = 12

But the result isn't the one expected as you can see :

The weeks 51 and 52 are supposed to be in the year 2021 and not the 2022.

Expected results :

What am I doing wrong?

You'd be better off doing it set based e.g. with a tally table and CTE. — Dale K, Oct 27 '21 at 07:10
`TPF_year` and `TPF_period_number` appear to be out of sync. Is it expected that `TPF_year` = 2022 starts from `TPF_period_id` >= 4? — Ed Harper, Oct 27 '21 at 07:15
This kind of thing https://stackoverflow.com/questions/40087627/sql-server-whole-weeks-total-in-a-calendar-month — Dale K, Oct 27 '21 at 07:19
But I need to work with the `@nb_period`, so I don't think this solution is better — Louis Chopard, Oct 27 '21 at 07:30
What is `TPF_year` ? Is it supposed to follow the calendar date ? What is the expected result ? — Squirrel, Oct 27 '21 at 07:57
A `WHILE` is *rarely* the right solution, @LouisChopard, let alone the "better" solution in SQL. SQL is a set based language, and a tally is set based. A rCTE is recursive, so in some way more like a `WHILE`, but it is at least a single statement rather than many, so they too are more often than not more performant that its iterative "friend". — Thom A, Oct 27 '21 at 08:03
You would be better off here showing us the results you want to, and sample data. We have the results you are recieving (as an image) but we don't know what results you are after; making this question difficult for us to answer when we don't know what the end solution is. — Thom A, Oct 27 '21 at 08:06

Carlos · Accepted Answer · 2021-10-27T19:14:37.000

This query returns what you need:

with T_period_futur  
AS
(
    SELECT 1 AS TPF_period_id, cast('25/10/21' as date) as TPF_start_period
    UNION ALL
    SELECT TPF_period_id + 1 as TPF_period_id, dateadd(week, 1, TPF_start_period) FROM T_period_futur WHERE TPF_period_id <12
)
select 
    TPF_period_id,
    YEAR(TPF_start_period) as TPF_year,
    cast(YEAR(TPF_start_period) as varchar) + '.' + RIGHT('00'+cast(datepart(wk,TPF_start_period)-1  as varchar),2) as TPF_period_number,
    TPF_start_period,
    DATEADD(d, 6, TPF_start_period) as TPF_end_period
from T_period_futur;

SQL Fiddle: http://sqlfiddle.com/#!18/9eecb/141635

Results:

| TPF_period_id | TPF_year | TPF_period_number | TPF_start_period | TPF_end_period |
|---------------|----------|-------------------|------------------|----------------|
|             1 |     2021 |           2021.43 |       2021-10-25 |     2021-10-31 |
|             2 |     2021 |           2021.44 |       2021-11-01 |     2021-11-07 |
|             3 |     2021 |           2021.45 |       2021-11-08 |     2021-11-14 |
|             4 |     2021 |           2021.46 |       2021-11-15 |     2021-11-21 |
|             5 |     2021 |           2021.47 |       2021-11-22 |     2021-11-28 |
|             6 |     2021 |           2021.48 |       2021-11-29 |     2021-12-05 |
|             7 |     2021 |           2021.49 |       2021-12-06 |     2021-12-12 |
|             8 |     2021 |           2021.50 |       2021-12-13 |     2021-12-19 |
|             9 |     2021 |           2021.51 |       2021-12-20 |     2021-12-26 |
|            10 |     2021 |           2021.52 |       2021-12-27 |     2022-01-02 |
|            11 |     2022 |           2022.01 |       2022-01-03 |     2022-01-09 |
|            12 |     2022 |           2022.02 |       2022-01-10 |     2022-01-16 |

@LouisChopard if my answer solved your problem, please mark it as accepted answer (and upvote it optionally). Thank you — Carlos, Nov 18 '21 at 22:09

Why does my week of the year's query show "2022.52" when it should show "2021.52"

1 Answers1