SQL Server 2017

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

create table project
(
    exposure int,
    period_start date,
    period_end date
)
insert into project values
( 1000000,	'01-Jan-2021',	'01-Jan-2023'),
(  500000,	'15-May-2021',	'15-May-2031'),
( 1000000,	'25-Mar-2022',	'24-Mar-2042'),
( 5000000,	'25-Mar-2022',	'25-Mar-2030'),
(10000000,	'01-Jan-2023',	'01-Jan-2033');
5 rows affected

; with years as
(
     select year = min(year(period_start)), max_year = max(year(period_end))
     from   project
     union all
     select year = year + 1, max_year
     from  years
     where  year < max_year
)
select y.year, sum(exposure)
from   project p
       cross join years y
where  year(p.period_start) <= y.year
and    year(p.period_end) >= y.year
group by y.year


year
(No column name)


2021
1500000

2022
7500000

2023
17500000

2024
16500000

2025
16500000

2026
16500000

2027
16500000

2028
16500000

2029
16500000

2030
16500000

2031
11500000

2032
11000000

2033
11000000

2034
1000000

2035
1000000

2036
1000000

2037
1000000

2038
1000000

2039
1000000

2040
1000000

2041
1000000

2042
1000000

year	(No column name)
2021	1500000
2022	7500000
2023	17500000
2024	16500000
2025	16500000
2026	16500000
2027	16500000
2028	16500000
2029	16500000
2030	16500000
2031	11500000
2032	11000000
2033	11000000
2034	1000000
2035	1000000
2036	1000000
2037	1000000
2038	1000000
2039	1000000
2040	1000000
2041	1000000
2042	1000000