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

user_16878379 0
Viewed 93

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 :

enter image description here

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

Expected results :

enter image description here

What am I doing wrong?

tsql sql-server sql
1 Answers

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 |
user_22328680
Related