有人可以建议我我们如何考虑一周在周日和周六结束,同时在52周的滚动报告中向后编号,例如Week1,Week2 .. Week2 .. Week52
我想从周日开始将当前一周计为第1周,因此,即使部分周仍然是第1周,上周周日 - 周日是周,依此类推,直到去年第52周)。我需要这个,因为我正在研究每日报告,该报告将在当前周和过去51周(全)周中寻找销售。我的报告还应在没有销售的" 0"的情况下返回任何一周。
这是一种方法。注意,我创建了递归CTE
来填充一些日期。您不必执行此步骤,而真正只需要YourWeekOrder = ...
部分。
declare @startDate date = dateadd(year,-1,getdate())
declare @endDate date = getdate()
;with cte as(
select @startDate as TheDate
union all
select dateadd(day,1,TheDate)
from cte
where TheDate < @endDate)
select
TheDate
,TheWeekOfYear = datepart(week,TheDate)
,YourWeekOrder = dense_rank() over (order by cast(datepart(year,TheDate) as char(4)) + case when len(datepart(week,TheDate)) = 1 then '0' + cast(datepart(week,TheDate) as char(2)) else cast(datepart(week,TheDate) as char(2)) end desc)
from cte
order by
TheDate
option(maxrecursion 0)
在此处查看IN