Subtract N from weeknum of current year-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

Try this:

DECLARE @Year       int         =   2022
    ,   @WeekNum    varchar(02) =   '02'
;

WITH FinalTable AS
    (
        SELECT  TOP 6 *
        FROM    WeeklyResults
        ORDER BY LTRIM(Year)   WeekNum DESC
    )

SELECT *
FROM    FinalTable  
ORDER BY Year, WeekNum

CodePudding user response：

Another option that doesn't involve an ORDER BY. Using DATEPART you can determine the final week of the prior year and subtract the number of weeks to get the records needed.

You may need to adjust what day is the first day of the week for your count. See this post for more info on that.

DECLARE
    @Year INT = 2022
    , @WeekNum INT = 3;

SELECT
    *
FROM
    WeeklyResults
WHERE
    Year = @Year
    AND WeekNum <= @WeekNum
    OR
        (
            @WeekNum < 6
            AND Year = @Year - 1
            AND WeekNum > DATEPART (WEEK, CONCAT ('12/31/', @Year - 1)) - @WeekNum
        );