SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table T (CenterCode varchar(3), DmeOrderDate date, DmeOrderNumberDisplay int identity(1, 1), DmeSignedByProvider int default(1), OrderType varchar(4) default ('NPWT'));
insert into T (CenterCode, DmeOrderDate) values
('C1', '2023-11-03'), ('C2', '2023-11-04'), ('C4', '2023-12-13'), ('C3', '2023-12-14'),
('C1', '2024-01-07'), ('C1', '2024-01-08'), ('C2', '2024-02-15'), ('C2', '2024-02-16'),
('C5', '2024-03-07'), ('C5', '2024-05-09'), ('C2', '2024-04-01');
11 rows affected

with monthly_centers as (
    select
        datediff(month, '2024-01-01', DmeOrderDate) as MonthNum, CenterCode,
        count(*) as OrderVolume
    from T --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), 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
order by "Month";


Month
NPWT_Order_Volume
NPWT_Active_Centers


2024-01-01 00:00:00.000
6
4

2024-02-01 00:00:00.000
6
4

2024-03-01 00:00:00.000
5
3

2024-04-01 00:00:00.000
4
2


with active_centers as (
    select
        datediff(month, '2024-01-01', DmeOrderDate) as MonthNum,
        DmeOrderDate, CenterCode, DmeOrderNumberDisplay
    from T --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;


report_date
NPWT_Order_Volume
NPWT_Active_Centers


2024-01-01
6
4

2024-02-01
6
4

2024-03-01
5
3

2024-04-01
4
2
Month	NPWT_Order_Volume	NPWT_Active_Centers
2024-01-01 00:00:00.000	6	4
2024-02-01 00:00:00.000	6	4
2024-03-01 00:00:00.000	5	3
2024-04-01 00:00:00.000	4	2