查找最后四个星期日的日期

Question

也就是说，我需要的是：

这里的关键是，它确实每周都会更改日期，从上周日到上周日的第二天等等，所以它是动态的。

如果有人能帮忙，我将不胜感激。

CASE
WHEN f.[STARTDATE] = last week Sunday (03/06) THEN '1 week ago'
WHEN f.[STARTDATE] = last 2 week Sunday (02/27) THEN '2 weeks ago'
WHEN f.[STARTDATE] = last 3 week Sunday (02/20) THEN '3 week ago'
WHEN f.[STARTDATE] = last 4 week Sunday (02/13) THEN '4 week ago'
END AS 'STARTDATE'

谢谢

Answer 1

首先，您需要获得最近的周日，然后才能从中减去7、14、21和28天。最简单的方法是检查一周中的当前日期并计算偏移量：0表示周日，1表示周一，依此类推：

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 case
when f.[startdate] = dateadd(day, -cte.offsett -  7, cast(current_timestamp as date)) then '1 week ago'
when f.[startdate] = dateadd(day, -cte.offsett - 14, cast(current_timestamp as date)) then '2 week ago'
when f.[startdate] = dateadd(day, -cte.offsett - 21, cast(current_timestamp as date)) then '1 week ago'
when f.[startdate] = dateadd(day, -cte.offsett - 28, cast(current_timestamp as date)) then '2 week ago'
end
from f
cross join cte