Convert Query Using Case Statement

select sum(DATEDIFF("D",M.ActiveStart,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))) as Duration_Days
    , m.HomeBranch, m.LocationName
from AX.Memberships M
where m.ActiveStart < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
and (M.ActiveEnd > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) or M.ActiveEnd is null)
group by m.HomeBranch, m.LocationName

I tried with CTE but results are different, I think query has some issue. Previous query is simple but I want to convert into CTE tables.

with CTE_ABC AS (
    select
        SUM(CASE 
            When m.ActiveStart < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) and (M.ActiveEnd > 
                DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,-1) or M.ActiveEnd is null)
            then DATEDIFF(D,M.ActiveStart,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)) else 0 end) as Total_Duration
        , M.HomeBranch
        , M.LocationName
    from AX.Memberships M 
    group by M.HomeBranch, M.LocationName
)
Select Total_Duration
From CTE_ABC 

Answer

Here’s the issue:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,-1) --> this is end of the month

is different to

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) --> this is start of the month

So your query will yield different result.

Your cte should look like below so it will have same result on your original query.

with CTE_ABC AS (
    select
    SUM(CASE 
        When m.ActiveStart< DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) and (M.ActiveEnd > 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) or M.ActiveEnd is null)
        then DATEDIFF(D,M.ActiveStart,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)) else 0 end) as 
        Total_Duration,
        M.HomeBranch,
        M.LocationName
        from AX.Memberships M 
    group by M.HomeBranch,M.LocationName
)
Select Total_Duration From CTE_ABC