How to convert this query to 52 weeks rolling based data.

Hi @RXR ，

You need to create a sql server job to perform operations regularly every week.
Does your query calculate the week number based on the current date of the system? If it is, then the problem is relatively simple. Every time job is automatically executed, the current week number will be calculated.

Best Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Like Echo I have problems understanding what you ask for. You talk about 52 weeks in the title, but the query you post seem to work with 17 weeks.

Is that you want want to change 17 to 52, 12 or whatever every time? In that case you can encapsulate the query into a function:

CREATE FUNCTION YourFunc(@weeks int) RETURNS TABLE AS
RETURN (
     With CTE_Deprivation_Live20 As (
      Select m.PeopleID ,DateDiff(yy,P.DateOfBirth,GetDate()) as Age, m.LocationName,m.ActiveStart,m.ActiveEnd,

      DatePart(wk,DateAdd(Week,@weeks, GETDATE())) WeekNo, DatePart(Year,GetDate() -1) year,
      sum(case when M.ActiveStart < DateAdd(day,-4,DateAdd(Week, @weeks ,GETDATE())) AND M.ActiveEnd >DateAdd(day,+2,DateAdd(Week,@weeks,GETDATE())) or M.ActiveEnd is null then 1 else 0 end) as LiveMem
        ,sum(case when M.ActiveEnd BETWEEN DateAdd(day,-4,DateAdd(Week,@weeks,GETDATE())) and DateAdd(day,+2,DateAdd(Week,@weeks,GETDATE()))  then 1 else 0 end) as No_of_Member_Cancelled

     From AX.Memberships m
     Join AX.People P
     On  M.PeopleID = P.PeopleID

     Group by m.PeopleIDm.LocationName,m.ActiveStart,m.ActiveEnd,p.SA1,p.DateOfBirth
     )
     Select * from CTE_Deprivation_Live20
)
go
SELECT * FROM YourFunc(52)

But this is a bit of a guess of what you are really asking for.