Here's a stab at what I think you are looking for

```
IF OBJECT_ID('tempdb..#ResultsFX') IS NOT NULL
DROP TABLE #ResultsFX
IF OBJECT_ID('tempdb..#SpotRates') IS NOT NULL
DROP TABLE #SpotRates
Create table #ResultsFX (Days_To_Maturity int,
BaseCurrency char(3),
SellCurrency char(3),
Daily_dif_Rate1 numeric(12,8))
Create table #SpotRates (RateDate date,
BaseCCY char(3),
RateCCY char(3),
MDInd char(1),
Rate numeric(12,8),
RateType varchar(10))
insert into #ResultsFX values
(6, 'COP','BGN', null),
(7, 'COP','BGN', null),
(30, 'COP','BGN', null),
(70, 'COP','BGN', null),
(6, 'INR','PEN', null),
(7, 'INR','PEN', null),
(30, 'INR','PEN', null),
(70, 'INR','PEN', null)
insert into #SpotRates values
('7/30/2020','COP','BGN','M',0.000438990,'7Day'),
('7/30/2020','COP','BGN','M',0.074353949,'2MNTH'),
('7/30/2020','COP','BGN','M',1.000247719,'1MNTH'),
('7/30/2020','COP','BGN','M',1.045677901,'3MNTH'),
('7/30/2020','INR','PEN','M',0.046835326,'7Day'),
('7/30/2020','INR','PEN','M',0.025950510,'2MNTH'),
('7/30/2020','INR','PEN','M',4.946421514,'1MNTH'),
('7/30/2020','INR','PEN','M',4.100394893,'3MNTH')
select r.BaseCurrency, r.SellCurrency, r.Days_To_Maturity, s.rate
from #ResultsFX r
join #SpotRates s
on r.BaseCurrency = s.BaseCCY
and r.SellCurrency = s.RateCCY
and s.RateType = Case when r.Days_To_Maturity >= 0 and r.Days_To_Maturity < 7
then '7Day'
when r.Days_To_Maturity >= 7 and r.Days_To_Maturity < 30
then '1MNTH'
when r.Days_To_Maturity >= 30 and r.Days_To_Maturity < 60
then '2MNTH'
end
select r.BaseCurrency, r.SellCurrency, r.Days_To_Maturity,
max(Case when r.Days_To_Maturity >= 0 and r.Days_To_Maturity < 7 and s.RateType = '7Day'
then s.rate
when r.Days_To_Maturity >= 7 and r.Days_To_Maturity < 30 and s.RateType = '1MNTH'
then s.rate
when r.Days_To_Maturity >= 30 and r.Days_To_Maturity < 60 and s.RateType = '2MNTH'
then s.rate
end) as Rate
from #ResultsFX r
join #SpotRates s
on r.BaseCurrency = s.BaseCCY
and r.SellCurrency = s.RateCCY
and s.RateType in ('7Day','1MNTH', '2MNTH')
Group by r.BaseCurrency, r.SellCurrency, r.Days_To_Maturity
```