即我需要的是：
這里的關鍵是它每周都會更改日期，從上周日到上周日的第二個等等，所以它是動態的。
如果有人可以提供幫助，將不勝感激。
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'
謝謝
uj5u.com熱心網友回復：
首先，您需要得到最近的星期日，然后才能從中減去 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/447531.html
上一篇：PHP每周到季度（日期）