How to query mssql to get weekly time interval from the first day the data was created for a period of 1 year

Question

Hello,

I am trying to write sql query to to get weekly time interval from the first day the data was created for a period of 1 year to select number of week of the current date for a particular user Id.

Below is my mssql query.

WITH WeeklyIntervals AS (
    SELECT 
        1 AS week_number,
        MIN(created_at) AS start_of_week,
        DATEADD(DAY, 6, MIN(created_at)) AS end_of_week
    FROM 
        your_table_name
    WHERE 
        user_id = @UserId
    UNION ALL
    SELECT 
        week_number + 1,
        DATEADD(DAY, 7, start_of_week),
        DATEADD(DAY, 13, start_of_week)
    FROM 
        WeeklyIntervals
    WHERE 
        DATEADD(DAY, 7, start_of_week) <= DATEADD(YEAR, 1, (SELECT MIN(created_at) FROM your_table_name WHERE user_id = @UserId))
)
SELECT 
    week_number,
    start_of_week,
    end_of_week,
    CASE 
        WHEN GETDATE() BETWEEN start_of_week AND end_of_week THEN 'Current Week'
        ELSE 'Past Week'
    END AS week_status
FROM 
    WeeklyIntervals
WHERE 
    GETDATE() BETWEEN start_of_week AND end_of_week;

When I try to run the query I get the following error message-

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'WeeklyIntervals'.

Please who can help me out of this issue, thank you in advance,

Answer 1

Hi @Emeka Okoye

Try this:

DECLARE @MIN_created_at DATE
SELECT @MIN_created_at= MIN(created_at) FROM  your_table_name WHERE user_id = @UserId
;WITH WeeklyIntervals AS (
    SELECT 
        1 AS week_number,
        MIN(created_at) AS start_of_week,
        DATEADD(DAY, 6, @MIN_created_at) AS end_of_week
    FROM 
        your_table_name
    WHERE 
        user_id = @UserId
    UNION ALL
    SELECT 
        week_number + 1,
        DATEADD(DAY, 7, start_of_week),
        DATEADD(DAY, 13, start_of_week)
    FROM 
        WeeklyIntervals
    WHERE 
        DATEADD(DAY, 7, start_of_week) <= DATEADD(YEAR, 1,@MIN_created_at )
)
SELECT 
    week_number,
    start_of_week,
    end_of_week,
    CASE 
        WHEN GETDATE() BETWEEN start_of_week AND end_of_week THEN 'Current Week'
        ELSE 'Past Week'
    END AS week_status
FROM 
    WeeklyIntervals
WHERE 
    GETDATE() BETWEEN start_of_week AND end_of_week;

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".