By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table #demo (Month char(7), Type varchar(10), Mileage int);
--low
insert into #demo values ('2021-05', 'low', 5000);
insert into #demo values ('2021-06', 'low', 15000);
insert into #demo values ('2021-07', 'low', 3000);
insert into #demo values ('2021-08', 'low', 3500);
-- med
insert into #demo values ('2021-05', 'med', 6789);
insert into #demo values ('2021-06', 'med', 12876);
insert into #demo values ('2021-07', 'med', 1578);
insert into #demo values ('2021-08', 'med', 3500);
--high
insert into #demo values ('2021-05', 'high', 5000);
insert into #demo values ('2021-06', 'high', 1500);
insert into #demo values ('2021-07', 'high', 2700);
insert into #demo values ('2021-08', 'high', 2968);
12 rows affected
select
Month
,max(case when Type='low' then Mileage end) as Month_Low
,max(case when Type='med' then Mileage end)as Month_Med
,max(case when Type='high' then Mileage end) as Month_High
,case when ROW_NUMBER() over(order by Month asc)>=3 Then 1 end as is_3month_data_present
from #demo
group by month
| Month | Month_Low | Month_Med | Month_High | is_3month_data_present |
|---|---|---|---|---|
| 2021-05 | 5000 | 6789 | 5000 | null |
| 2021-06 | 15000 | 12876 | 1500 | null |
| 2021-07 | 3000 | 1578 | 2700 | 1 |
| 2021-08 | 3500 | 3500 | 2968 | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.
select
Month
,case when is_3month_data_present=1 then Sum(Month_Low+Month_Med+Month_High) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW ) end as ThreeMonth_TotalMileage
,case when is_3month_data_present=1 then Sum(Month_Low) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW ) end as ThreeMonth_Low
,case when is_3month_data_present=1 then Sum(Month_Med ) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW ) end as ThreeMonth_Med
,case when is_3month_data_present=1 then Sum(Month_High) over(order by Month ROWS BETWEEN 2 PRECEDING and CURRENT ROW ) end as ThreeMonth_High
from
(select
Month
,Max(case when Type='low' then Mileage end) as Month_Low
,Max(case when Type='med' then Mileage end)as Month_Med
,Max(case when Type='high' then Mileage end) as Month_High
,case when ROW_NUMBER() over(order by Month asc)>=3 Then 1 end as is_3month_data_present
from #demo
group by Month
)stg
| Month | ThreeMonth_TotalMileage | ThreeMonth_Low | ThreeMonth_Med | ThreeMonth_High |
|---|---|---|---|---|
| 2021-05 | null | null | null | null |
| 2021-06 | null | null | null | null |
| 2021-07 | 53443 | 23000 | 21243 | 9200 |
| 2021-08 | 46622 | 21500 | 17954 | 7168 |
Warning: Null value is eliminated by an aggregate or other SET operation.