SQL Server 2019

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

with dates ( coddate ) as

( select convert(datetime , '15/09/2018' , 103) union all

select convert(datetime , '16/09/2018' , 103) union all

select convert(datetime , '17/09/2021' , 103) union all

select convert(datetime , '18/09/2021' , 103) union all

select convert(datetime , '19/02/2021' , 103)

) ,

rates ( rate, id ) as

(

select 'ConfD1' , 46 union all

select 'ConfD2' , 47

rateprices ( Rate , Id, Startdate , EndDate, price )

( select 'ConfD1' , 46 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 111 union all

select 'ConfD1' , 46 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 222 union all

select 'ConfD2' , 47 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 333 union all

select 'ConfD2' , 47 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 444 union all

select 'ConfD3' , 48 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 555 union all

select 'ConfD3' , 48 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 666

rates_and_dates ( rate , id , coddate )

( select a.rate , a.id , b.coddate from rates a cross join dates b

result ( rate, id, coddate, price )

(

select a.rate , a.id , a.coddate , price = coalesce(b.price, 0)

from rates_and_dates a inner join rates c

on a.rate=c.rate

left join rateprices b

on a.rate=b.rate and b.startdate <= a.coddate and b.enddate >= a.coddate

)

select rate, coddate, price from result

order by 2, 1