Compounding values from previous months

My manager wants me count each center that has an order. If a center make one order within the beginning of the last two months, it is considered active for the next two months from the month that it made an order. Example: if center 'AR238' made an order February 1rst, then it is considered active for the next two months. So it is active until the end of April if it does not make another order in march or April. I am trying to count each active center for each month, compounding from the previous months. So if a center made an order in February, I need to be able to count that for Feb, Mar, and April. Please assist on how to do that. Also I need the query to be included into the query I already have written out. This query will just add an extra column to the table I already have from my original query. I will post my original query below.

WITH active_centers AS (
SELECT DateName( month , DateAdd( month , MONTH(dmeorderdate) , 0 ) - 1 ) as Month, 
count(distinct DmeOrderNumberDisplay) as NPWT_Order_Volume,  
count(distinct CenterCode) as NPWT_Active_Centers

FROM [AtHome].[Reporting].[WoundQOrderDetails] w
left join[AtHome].[Reporting].[DimCenter] c on c.CenterSK = w.CenterSK
where DmeOrderDate between '2024-01-01' and GETDATE()
and DmeSignedByProvider = 1
and OrderType = 'NPWT'
and CenterCode is not null
group by DateName( month , DateAdd( month , MONTH(dmeorderdate) , 0 ) - 1 ),MONTH(dmeorderdate)
order by MONTH(dmeorderdate))

-- This query returns each month, the order volume of each month, and the active centers for each month. The active centers I have in this query already is only counting the center once. I want an extra column that count each individual center for jan, feb, march if it made an order in january. If that center made another order in feburary, it will reset the cycle, we will now count it once for feb, march, april. The extra column I am trying to add is basically compounding.

Solution

This doesn't keep your query intact the way you hoped but I think you're going to want something like this. First of all, your version of the query isn't going to handle more than 12 months of data as it sits. And to get the figures for the first two months you've also got to look back before the start of the reporting range if I'm understanding the requirement. Because of the count(distinct) operations you won't be able to pre-aggregate and so I think a subquery/cross apply is the way to do this part of the tabulation:

 with active_centers as (
    select
        datediff(month, '2024-01-01', DmeOrderDate) as MonthNum,
        DmeOrderDate, CenterCode, DmeOrderNumberDisplay
    from AtHome.Reporting.WoundQOrderDetails as w
        left join AtHome.Reporting.DimCenter c on c.CenterSK = w.CenterSK
    where DmeOrderDate between dateadd(month, -2, '2024-01-01') and getdate()
        and DmeSignedByProvider = 1 and OrderType = 'NPWT' and CenterCode is not null
), report_dates(report_date) as (
    select distinct datetrunc(month, DmeOrderDate)
    from active_centers where MonthNum >= 0
)
select report_date, NPWT_Order_Volume, NPWT_Active_Centers
from report_dates as d cross apply (
    select count(distinct DmeOrderNumberDisplay), count(distinct CenterCode)
    from active_centers as c
    where c.DmeOrderDate >= dateadd(month, -2, d.report_date) and
          c.DmeOrderDate  < dateadd(month,  1, d.report_date)
) as cnt(NPWT_Order_Volume, NPWT_Active_Centers)
order by report_date;

Since there's probably an index on the order date column I'm hoping this will efficiently use that index in the loop.

If your DmeOrderNumberDisplay is already a unique value then counting distinct values is unnecessary for that column. This may be a better option then. (I also suspect you don't need that outer join.):

with monthly_centers as (
    select
        datediff(month, '2024-01-01', DmeOrderDate) as MonthNum, w.CenterCode,
        count(*) as OrderVolume
    from AtHome.Reporting.WoundQOrderDetails as w
        inner join AtHome.Reporting.DimCenter c on c.CenterSK = w.CenterSK
    where DmeOrderDate between dateadd(month, -2, '2024-01-01') and getdate()
        and DmeSignedByProvider = 1 and OrderType = 'NPWT'
    group by datediff(month, '2024-01-01', DmeOrderDate), w.CenterCode
)
select distinct dateadd(month, MonthNum, '2024-01-01') as "Month",
    NPWT_Order_Volume, NPWT_Active_Centers
from monthly_centers as mc cross apply (
    select sum(OrderVolume), count(distinct CenterCode)
    from monthly_centers as mc2
    where mc2.MonthNum between mc.MonthNum - 2 and mc.MonthNum
) as lookback(NPWT_Order_Volume, NPWT_Active_Centers)
where MonthNum >= 0
group by MonthNum
order by "Month";

Both queries are returning the same results against a small data set here: https://dbfiddle.uk/vOhchhCd