How to convert month to week to this query

;With CTE_Mem as (
Select m.PeopleID , m.Operator, m.LocationName,     
  sum(case when M.ActiveStart < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND M.ActiveEnd 
 > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) then 1 else 0 end) as No_of_Live_Member , 
 sum(case when M.ActiveEnd BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) and 
 DATEADD(month, DATEDIFF(MONTH, 0,Getdate())  , -1)  then 1 else 0 end) as No_of_Member_Cancelled
From dbo.Memberships m
Group by m.PeopleID , m.Operator, m.LocationName
 ) 
Select * from CTE_Mem

I want to calculate the last month Aug 1st to Aug 31 weeks and convert the above query month to week and shows also which week is?

Please check this for tabular format results:

Format results

Answer

I think I know what you are attempting to do, however, you lack details in your schema and there are gaps on how you want to obtain the final results. Here is a basic date math query that partitions date by an id ranked within each month/year combination found in the data. This might help you get from A->B.

DECLARE @T TABLE (ID INT, ActiveStart DATETIME)

INSERT @T VALUES 
    (1,'08/05/2020'),
    (1,'08/10/2020'),
    (1,'08/20/2020'),
    (2,'08/20/2020'),
    (2,'08/20/2021')
;
WITH WeekValuesWithID AS
(
    SELECT
        ID,
        YearNumber = DATEPART(YEAR,ActiveStart),
        MonthNumber = DATEPART(MONTH,ActiveStart),
        WeekNumberInMonth = RANK() OVER (PARTITION BY DATEPART(YEAR,ActiveStart), DATEPART(MONTH,ActiveStart) ORDER BY DATEPART(WEEK,ActiveStart))
    FROM
        @T
)

SELECT
    IDCount = COUNT(ID),
    WeekNumberInMonth,
    MonthNumber,
    YearNumber
FROM
    WeekValuesWithID
GROUP BY
    WeekNumberInMonth,
    MonthNumber,
    YearNumber