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,

Accepted Answer

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".

Share via

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

0 additional answers

Your answer