CODEWITHSUNDEEP
sqlt-sql
Sanj
Sanj

Reputation: 45

52weeks rolling with running week data

Can someone suggest me how do we consider week to start on Sunday and end on Saturday, while numbering them backwards in a 52 week rolling report like week1, week2.. week52

I want to count my current week as Week1 starting on Sunday, so even if its partial week its still week1 and last week Sunday-Saturday is week2 and so on until 52nd week last year (that would roughly be in September counting backwards). I need this as I am working on a daily report that will look for sales for current week and past 51 (full) weeks. My report should also return any week without sales '0' without skipping it.

Upvotes: 1

Views: 1342

Answers (1)

S3S
S3S

Reputation: 25112

Here is a way. Note I created the recursive CTE to populate some dates. You won't have to do this step, and real only need the YourWeekOrder = ... part.

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)

SEE IT IN ACTION HERE

Upvotes: 2

Related Questions