SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table delivery_db (Quarter varchar(7),Type varchar(10)
   , Location varchar(10),Total int);
insert into delivery_db values
 ('2024-Q1','TypeA','TBD',	10)
,('2024-Q1','TypeA','TBD',	4)
,('2024-Q4','TypeA','TBD',	5)
,('2024-Q1','TypeB','TBD',	7)
,('2024-Q3','TypeB','TBD',	8)
,('2024-Q3','TypeB','TBL',	3)
,('2024-Q1','TypeB','TBX',	33)
,('2024-Q4','TypeB','TBY',	51)
,('2024-Q1','TypeZ','TBZ',	 1)
,('2024-Q2','TypeZ','TBZ',	 1)
,('2024-Q3','TypeZ','TBZ',	 1)
,('2024-Q4','TypeZ','TBZ',	 1)
;
select * from delivery_db;


Quarter
Type
Location
Total


2024-Q1
TypeA
TBD
10

2024-Q1
TypeA
TBD
4

2024-Q4
TypeA
TBD
5

2024-Q1
TypeB
TBD
7

2024-Q3
TypeB
TBD
8

2024-Q3
TypeB
TBL
3

2024-Q1
TypeB
TBX
33

2024-Q4
TypeB
TBY
51

2024-Q1
TypeZ
TBZ
1

2024-Q2
TypeZ
TBZ
1

2024-Q3
TypeZ
TBZ
1

2024-Q4
TypeZ
TBZ
1


select quarter, type, location, sum(total) total
from delivery_db
group by quarter, type, location
order by type, location,quarter


quarter
type
location
total


2024-Q1
TypeA
TBD
14

2024-Q4
TypeA
TBD
5

2024-Q1
TypeB
TBD
7

2024-Q3
TypeB
TBD
8

2024-Q3
TypeB
TBL
3

2024-Q1
TypeB
TBX
33

2024-Q4
TypeB
TBY
51

2024-Q1
TypeZ
TBZ
1

2024-Q2
TypeZ
TBZ
1

2024-Q3
TypeZ
TBZ
1

2024-Q4
TypeZ
TBZ
1


with forecasts as (
  select quarter, type, location, sum(total) total
    from delivery_db
    group by quarter, type, location
)
,quarters AS (
    SELECT *
    FROM (
        VALUES
            ('2024-Q1'),
            ('2024-Q2'),
            ('2024-Q3'),
            ('2024-Q4')
    ) v(quarter)
)
,AllPoints as(
   select * 
   from quarters,
    (select distinct type,location from forecasts)f
)
-- select * from allpoints;
-- select * from quarters;
-- select * from forecasts;
SELECT
    q.quarter,
    q.type,
    q.location,
    SUM(case when f.quarter=q.quarter then total else 0 end) 
       OVER(PARTITION BY q.type,q.location ORDER BY q.quarter
     ) AS total2,
     f.quarter,
     f.total qtl_total
FROM AllPoints q
left join forecasts f on q.quarter= f.quarter
    and q.type=f.type and q.location=f.location
order by type,location,q.quarter


quarter
type
location
total2
quarter
qtl_total


2024-Q1
TypeA
TBD
14
2024-Q1
14

2024-Q2
TypeA
TBD
14
null
null

2024-Q3
TypeA
TBD
14
null
null

2024-Q4
TypeA
TBD
19
2024-Q4
5

2024-Q1
TypeB
TBD
7
2024-Q1
7

2024-Q2
TypeB
TBD
7
null
null

2024-Q3
TypeB
TBD
15
2024-Q3
8

2024-Q4
TypeB
TBD
15
null
null

2024-Q1
TypeB
TBL
0
null
null

2024-Q2
TypeB
TBL
0
null
null

2024-Q3
TypeB
TBL
3
2024-Q3
3

2024-Q4
TypeB
TBL
3
null
null

2024-Q1
TypeB
TBX
33
2024-Q1
33

2024-Q2
TypeB
TBX
33
null
null

2024-Q3
TypeB
TBX
33
null
null

2024-Q4
TypeB
TBX
33
null
null

2024-Q1
TypeB
TBY
0
null
null

2024-Q2
TypeB
TBY
0
null
null

2024-Q3
TypeB
TBY
0
null
null

2024-Q4
TypeB
TBY
51
2024-Q4
51

2024-Q1
TypeZ
TBZ
1
2024-Q1
1

2024-Q2
TypeZ
TBZ
2
2024-Q2
1

2024-Q3
TypeZ
TBZ
3
2024-Q3
1

2024-Q4
TypeZ
TBZ
4
2024-Q4
1

Quarter	Type	Location	Total
2024-Q1	TypeA	TBD	10
2024-Q1	TypeA	TBD	4
2024-Q4	TypeA	TBD	5
2024-Q1	TypeB	TBD	7
2024-Q3	TypeB	TBD	8
2024-Q3	TypeB	TBL	3
2024-Q1	TypeB	TBX	33
2024-Q4	TypeB	TBY	51
2024-Q1	TypeZ	TBZ	1
2024-Q2	TypeZ	TBZ	1
2024-Q3	TypeZ	TBZ	1
2024-Q4	TypeZ	TBZ	1