Create table with the week numbers of the last 6 months, with their respective month and year. SQL Server

I’m trying to get a table that contains the week number of the last 6 months. I found some code in github, and I modified it to accomplish my goal.

This what I have:

-- choosing date to start with
DECLARE @start_date DATE = DATEADD(MONTH, -6, CURRENT_TIMESTAMP)

SELECT DISTINCT  
    DATEPART(WEEK, DATEADD(DAY, Number, @start_date)) AS week_number, 
    DATEPART(YEAR, DATEADD(DAY, Number, @start_date)) AS year, --adding year number to order by year
    DATEPART(MONTH, DATEADD(DAY, Number, @start_date)) AS month_number
FROM  
    master..spt_values 
WHERE 
    Type = 'P'
    AND DATEADD(DAY, Number, @start_date) <= CURRENT_TIMESTAMP 
    AND DATEPART(YEAR, DATEADD(DAY, Number, @start_date)) > 2019 

— 2019 only used to show the info from 2020 and above, because I don’t want the week number of 2019 or below

I get this result :

result of the query

The problem is that week 5 is repeated because apparently that week number is between 2 different months, but I just want to keep one of the results no matter the month or year, I want the week number to appear just once.

Is there a way to do this?

Answer

Sure, use GROUP BY in your query and pick either the MAX or MIN value of month_number for each year and week_number. Here’s an example, but I’m using a recursive CTE rather than that clunky query that relies on a table that has a limited number of numbers in it.

WITH cte AS (
    SELECT
        @start_date AS [date],
        DATEPART(WEEK, @start_date) AS week_number, 
        DATEPART(YEAR, @start_date) AS [year],
        DATEPART(MONTH, @start_date) AS month_number
    UNION ALL
    SELECT
        DATEADD(DAY, 1, [date]) AS [date],
        DATEPART(WEEK, DATEADD(DAY, 1, [date])) AS week_number, 
        DATEPART(YEAR, DATEADD(DAY, 1, [date])) AS [year],
        DATEPART(MONTH, DATEADD(DAY, 1, [date])) AS month_number
    FROM
        cte
    WHERE
        [date] <= GETDATE())
SELECT
    [year],
    MAX(month_number) AS month_number,
    week_number
FROM
    cte
WHERE
    [year] > 2019
GROUP BY
    [year],
    week_number
ORDER BY
    1, 2, 3 OPTION (MAXRECURSION 0);

I get these results:

year    month_number    week_number
2020    1               1
2020    1               2
2020    1               3
2020    1               4
2020    2               5
2020    2               6
2020    2               7
2020    2               8
2020    2               9
2020    3               10
2020    3               11
2020    3               12
2020    3               13
2020    3               14

If I chose MIN instead of MAX I would get the same results, but week 5 would be assigned to month 1.