T-SQL Retrieve Last 6 Weeks Data to the Previous Saturday

user_17832612 0
Viewed 50
SELECT TOP 100 *
FROM FactSalesDetail
WHERE TradingDate >= DATEADD(ww, -6, (Select MAX([TradingDate]) From FactSalesDetail))
ORDER BY TradingDate

Can anyone advise how I can convert the above WHERE Clause from retrieving the last 6 weeks data from Max Date in my Fact Table to the last 6 weeks to the Previous Saturday?

So as of Today that would Saturday 1st Jan and then back 6 weeks from that?

where-clause date tsql
1 Answers

Using the current weekday can get you last Saturday.
And if you bring @@DATEFIRST into the equation then it won't depend on the DATEFIRST setting.

SELECT TOP 100 *
FROM FactSalesDetail
WHERE TradingDate >= CONVERT(DATE, DATEADD(WEEK, -6, DATEADD(DAY, -(@@DATEFIRST+DATEPART(WEEKDAY,GETDATE()))%7, 
GETDATE())))
  AND TradingDate <= CONVERT(DATE, DATEADD(DAY, -(@@DATEFIRST+DATEPART(WEEKDAY,GETDATE()))%7, 
GETDATE()))
ORDER BY TradingDate

Test snippet for date range

SET DATEFIRST 7;

SELECT 
  datename(weekday, date_now) AS weekday_now, date_now
, datename(weekday, date1) AS wd1, date1
, datename(weekday, date2) AS wd2, date2 
FROM
(
    SELECT 
      CAST(GETDATE() AS DATE) AS date_now
    , CONVERT(DATE, DATEADD(WEEK, -6, DATEADD(DAY, -(@@DATEFIRST+DATEPART(WEEKDAY,GETDATE()))%7, 
GETDATE()))) AS date1
    , CONVERT(DATE, DATEADD(DAY, -(@@DATEFIRST+DATEPART(WEEKDAY,GETDATE()))%7, 
GETDATE())) AS date2
) q
weekday_now date_now wd1 date1 wd2 date2
Tuesday 2022-01-04 Saturday 2021-11-20 Saturday 2022-01-01

db<>fiddle here

user_40034190
Related