sql - How to get six weeks data from a week column?

Question

I have a legacy query in which I am looking data for six weeks as shown below. In my below AND condition I get data for past six weeks and it worked fine in 2020 middle and end. But since 2021 started, this stopped working because of obvious subtraction I am doing with 6.

AND data.week_col::integer BETWEEN DATE_PART(w, CURRENT_DATE) - 6 AND DATE_PART(w, CURRENT_DATE) - 1

There is a bug in above query because of which it stopped working in 2021. How can I change above condition so that it can work entire year without any issues and give me data for past 6 weeks.

Update

Below is my query which I am running:

select *,
dateadd(d, - datepart(dow, trunc(CONVERT_TIMEZONE('UTC','PST8PDT',client_date))), trunc(CONVERT_TIMEZONE('UTC','PST8PDT',client_date)) + 6) as day,
date_part(week, day) as week_col
from holder data
where data.week_col::integer BETWEEN DATE_PART(w, CURRENT_DATE) - 6 AND DATE_PART(w, CURRENT_DATE) - 1

client_date column has values like this - 2021-01-15 21:30:00.0. And from that I get value of day column and from day column I get value of week_col column as shown above.

week_col column has values like 53, 52 .... It's a week number in general.

Because of my AND condition I am getting data for week 1 only but technically I want data for 49, 50, 51, 52, 53 and 1 as it is past six weeks. Can I use day column here to get correct past six weeks?

Answer 1

Would this serve as a solution? I do not know much about the redshirt syntax but I read it supports dateadd(). If you are normalizing client_date to a time zone converted day with no time then why not simply use that in the comparison to the current date converted to the same time zone.

WHERE
    client_date BETWEEN
    DATEADD(WEEK,-6,trunc(CONVERT_TIMEZONE('UTC','PST8PDT',CURRENT_DATE)))
    AND
    DATEADD(WEEK,-1,trunc(CONVERT_TIMEZONE('UTC','PST8PDT',CURRENT_DATE)))

If the above logic works out then you may want to convert the -6 and -1 week to variables, if that is supported.

Solution 2

This is a bit more verbose but involves virtualizing a calender table and then joining your current date parameter into the calender data, for markers. Finally, you can join your data against the calender which has been normalized by weeks in time chronologically.

This is SQL Server syntax, however, I am certain it can be converted to RS.

DECLARE @D TABLE(client_date DATETIME)
INSERT @D VALUES
('11/20/2020'),('11/27/2020'),
('12/4/2020'),('12/11/2020'),('12/18/2020'),('12/25/2020'),
('01/8/2021'),('01/8/2021'),('1/15/2021'),('1/22/2021'),('1/29/2021')

DECLARE @Date DATETIME = '1/23/2021'
DECLARE @StartDate DATETIME = '01/01/2010'
DECLARE @NumberOfDays INT = 6000

;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    R2(N) AS (SELECT 1 FROM R1 a, R1 b),
    R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
    Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3)
    
,WithTally AS   
(
    SELECT CalendarDate = DATEADD(DAY,T.Number,@StartDate)
    FROM Tally T
    WHERE T.Number < @NumberOfDays
)   
,Calendar AS
(
    SELECT
        CalendarDate,
        WeekIndex = DENSE_RANK() OVER(ORDER BY  DATEPART(YEAR, CalendarDate), DATEPART(WEEK, CalendarDate))
    FROM
        WithTally                   
),
CalendarAlignedWithCurrentDateParamater AS
(
    SELECT *
    FROM
        Calendar
        CROSS JOIN (SELECT WeekIndexForToday=WeekIndex FROM Calendar WHERE Calendar.CalendarDate=@Date  ) AS X
)
SELECT 
    D.*,
    C.WeekIndex,
    C.WeekIndexForToday
FROM 
    CalendarAlignedWithCurrentDateParamater C
    INNER JOIN @D D ON D.client_date = C.CalendarDate
WHERE 
    C.WeekIndex BETWEEN C.WeekIndexForToday-6 AND C.WeekIndexForToday-1 
    
OPTION (MAXRECURSION 0)