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 |