Last Sunday to last Saturday date calculation (not in where statement, but select) SQL-CodePudding

I see a lot of examples how to calculate dates from last Sunday to last Saturday in a where statement, but I didn't find any for the last week in SELECT statement.

if I need to have a formula [U_ONSALE_DATE] between last Sunday (03/06) and last Saturday (03/12) so that it dynamically changes each week (Sun - Sat).

I was trying to do it this way, but it didn't work as expected.

[U_ONSALE_DATE] BETWEEN  DATEDIFF(day,getdate(),f.[STARTDATE]) = -1 AND DATEDIFF(day,getdate(),f.[STARTDATE]) = -6

I have such a visual. Titles On Sale Last Week.

enter image description here

The way how I was calculating the dates before in a SELECT statement was with the CASE statement.

,CASE WHEN DATEADD(DAY,0,DATEDIFF(DAY,0,[U_ONSALE_DATE])) 
<= DATEADD(DAY,-7,DATEDIFF(DAY,0,GETDATE())) 
AND 
DATEADD(DAY,0,DATEDIFF(DAY,0,[U_ONSALE_DATE])) 
> DATEADD(DAY,-14,DATEDIFF(DAY,0,GETDATE()))
THEN 'Last Week On Sale Date'
ELSE NULL END AS 'Last Week On Sale Date'

However, this case statement basically takes titles from -7 (one week ago) to - 14 (2 weeks ago). But this is not actually what I need, I need this case statement to take [U_ONSALE_DATE] and take dates from Sunday to Saturday of the last week and it obviously will be changing each week. like today last Sun to Sat is 03/06 to 03/12.

CodePudding user response：

I'm still not clear what you want, but hopefully this will help. Determine the previous Saturday (regardless of @@DATEFIRST settings). You want an open-ended range, since probably U_ONSALE_DATE is datetime, so you want everything after and including the beginning of last Sunday and before the beginning of the current Sunday. So:

DECLARE @d date = GETDATE();
DECLARE @PrevSat date = DATEADD(DAY, -(DATEPART(WEEKDAY, @d) @@DATEFIRST)%7, @d);
    
SELECT [Last Week On Sale Date] = CASE 
  WHEN U_ONSALE_DATE >= DATEADD(DAY, -6, @PrevSat) -- >= PrevSund
   AND U_ONSALE_DATE <  DATEADD(DAY, 1,  @PrevSat) -- <  CurrSund
  THEN 'Last Week On Sale Date' END
FROM ...

If PowerBI doesn't let you declare variables, well, shame on PowerBI:

SELECT U_ONSALE_DATE, [Last Week On Sale Date] = CASE 
  WHEN U_ONSALE_DATE >= DATEADD(DAY, -6, d.PrevSat) -- >= PrevSund
   AND U_ONSALE_DATE <  DATEADD(DAY, 1,  d.PrevSat) -- <  CurrSund
  THEN 'Last Week On Sale Date' END
FROM ...
CROSS APPLY
(
  SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, 
    CONVERT(date,GETDATE())) @@DATEFIRST)%7, 
    CONVERT(date,GETDATE()))
) AS d(PrevSat);

Example db<>fiddle

CodePudding user response：

First you need to get the closest Sunday that is less than today, then subtract 7 days and 1 day to get previous Sunday and Saturday.

Here is an example of the calculation written as a cte. You can use the logic inside cross apply or declared variables:

with cte as (
    select offsett = case datename(weekday, cast(current_timestamp as date))
        when 'sunday'    then 0
        when 'monday'    then 1
        when 'tuesday'   then 2
        when 'wednesday' then 3
        when 'thursday'  then 4
        when 'friday'    then 5
        when 'saturday'  then 6
    end
)
select *
from t
cross join cte
where date_col >= dateadd(day, -cte.offsett - 7, cast(current_timestamp as date))
and   date_col <= dateadd(day, -cte.offsett - 1, cast(current_timestamp as date))