Select date within respective range in sql server

Question

Hello,
I have 2 tables, fee rate and Loan table,
Fee rate
Fee approved Date From To Fee
01/12/2019 0 - 1,500 10
21/12/2019 0 - 1,500 8
02/01/2020 1,501 5,000 15

and
Loan Table
ID Dates Amount
365002 20/12/2019 1343.61
477003 08/01/2020 3298.65

How can I construct a select statement to select respective Fee from Fee rate combine with Loan table where Loan Dates is slightly after Fee approved Date as show in Result table. Here Fee of 10 is select because loan dates is 20/12/2019 which is greater that the approved date (01/12/2019) but smaller that the subsequent approved date which is 21/12/2019

Result table
ID Loan Dates Amount Fee
365002 20/12/2019 1343.61 10
477003 08/01/2020 3298.65 15

Answer 1

Please check:

CREATE TABLE #Feerate([Fee approved Date] DATE,[From] INT, [To] INT,Fee INT)  
INSERT INTO #Feerate VALUES('12/01/2019', 0 ,- 1500, 10)  
                           ,('12/21/2019', 0 ,- 1500 ,8)  
    ,('01/02/2020', 1501, 5000,15)  
  
CREATE TABLE #Loan (ID INT,Dates DATE, Amount DECIMAL(6,2))  
INSERT INTO #Loan VALUES(365002,'12/20/2019', 1343.61)  
                           ,(477003,'01/08/2020', 3298.65)  
  
SELECT * FROM #Feerate  
SELECT * FROM #Loan  
  
;WITH cte  
as(SELECT *  
FROM #Loan   
CROSS APPLY #Feerate)  
,cte2 as(SELECT *,MAX([Fee approved Date]) OVER(PARTITION BY ID ORDER BY ID ) ma  
FROM cte  
WHERE Dates>[Fee approved Date])  
  
SELECT ID,Dates,Amount,Fee FROM cte2   
WHERE [Fee approved Date]=ma  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.