SQL Server How to get weeks between two years and two week numbers-CodePudding

I have a table with the following data:

user_id year    week_number
1       2021    35
1       2021    27
1       2021    22
1       2021    50
1       2022    1
1       2022    7
1       2022    11
1       2022    15
1       2022    19
1       2022    2
1       2021    52
1       2022    3
1       2022    4
8       2021    48
8       2021    49
8       2021    50
8       2021    51
8       2021    52
8       2022    1
8       2022    2
8       2022    3

Now I want to get all weeks between year1, week1 and year2, week2. Variables could for example be:

-- Only get weeks from specific user. In this example its user 1.
userID = 1
year1 = 2021
week1 = 52
year2 = 2022
week2 = 05

I first had the following query:

-- Where 2021, 2022, 52 and 05 are the variables.
SELECT 
    * 
FROM 
    [db].[dbo].[table] 
WHERE 
    [user_id] = 1 
AND
    [year] BETWEEN '2021' AND '2022'
AND
    [week_number] BETWEEN '52' AND '05'

This worked fine when I only had 1 year to select between. But in this example I use multiple years. Now week 52 belongs to year 2021 and week 05 to 2022. No result is now shown.

I also made a working query, but it asks extra parameters:

-- Here I have 2 extra week numbers I have to give.
SELECT 
    * 
FROM 
    [db].[dbo].[table] 
WHERE 
    [user_id] = 1
AND
    ([year] = '2021'
AND
    [week_number] BETWEEN '52' AND '52'
OR
    [year] = '2022'
AND 
    [week_number] BETWEEN '01' AND '05')

I don't think this is a very good solution because I would have to write an or for every extra year in between. I belive things could be simplified here, but dont know how.

So, is there a way to get the weeks that are between my variables? Without changing my table structure.

CodePudding user response：

A small calculation may help:

...
WHERE
   ([user_id] = 1) AND
   ([year] * 100   [week_number]) BETWEEN 202152 AND 202205

CodePudding user response：

You could...use variables:

DECLARE @myUser int = 1, 
        @startYear int = 2021,
        @endYear int = 2022,
        @startWeek int = 5,
        @endWeek INT = 13;


SELECT * 
FROM [db].[dbo].[table] 
WHERE [user_id] = @myUser
      AND (
             (@startYear = [year] AND @startWeek = [week_number] AND @startYear = @endYear AND @startWeek = @endWeek) --the selection is only one week
           OR ([year] = @startYear AND [week_number] >= @startWeek AND [week_number] <= @endYear AND @startYear = @endYear AND @endWeek > @startWeek) --same year, multiple weeks
           OR (@endYear > @startYear -- Spans multiple years
                  AND ( 
                         ([year] >= @startYear AND [year] < @endYear AND [week_number] >= @startWeek ) --anything after start week for each year that is before the end year
                         OR ([year] <= @endYear AND [year] > @startYear AND [week_number] <= @endWeek) --anything before end week for any year after the start year
                      )
           );