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)