Subtract N from weeknum of current year

We have table WeeklyResults that looks like this:

Year    WeekNum   Value
2021    47        11.0
2021    48        14.0
2021    49        12.0
2021    50        17.0
2021    51        11.8
2021    52        11.3
2021    53        11.1
2022    01        11.5
2022    02        11.5  
2022    03        81.5  

We have a report with two parameters: Date and WeekNum. The report needs to show the last 6 weeks based on the weeknumber selected.

The issue is that, if user selects Week 2 of 2022, how can I subtract 6 weeks so that I get weeks 50, 51, 52, 53 of 2021 and weeks 1, 2 from 2022?

So, if the user selects 2022 and Week 02, it would show the last 6 weeks based on Weeknum 2 of year 2022 (wk50 to Wk02). If user selects 2021 and 52, it would show wk47-52.

Solution 1:

You can use a little < and <= logic along side TOP and ORDER BY to achieve this:

DECLARE @Year int = 2022, 
        @WeekNum int = 3; --Note, if you are storing WeekNum as a (var)char,
                          --your leading zeros imply you are, then define the
                          --variable as a char(2).

SELECT TOP (6)
       [Year],
       WeekNum,
       [Value]
FROM dbo.YourTable
WHERE ([Year] = @Year AND WeekNum <= @WeekNum)
   OR [Year] < @Year
ORDER BY [Year] DESC,
         WeekNum DESC;