rank between dates

Question

I have a table that holds historical information, below.

TableA

ID    Set_Date  Reason     pk
 1      1/1/17   denied    1
 1      1/2/17   approved  2

TableB

StartDate    EndDate    ID
  1/1/17      1/2/17     1

I need the latest value when the set_date is between the startDate and EndDate

Example:

SELECT * 
  FROM(
SELECT *, DENSE_RANK() OVER (PARTITION BY ID ORDER BY SET_DATE DESC) AS RNK
FROM TableB B
INNER JOIN TABLEA ON B.ID = A.ID 
  AND A.SET_DATE BETWEEN B.STARTDATE AND B.ENDDATE
    --BUT TWO VALUES APPLY HERE AND I ONLY WANT ONE VALUE, THE LATEST ENTRY OF THE SET OF DATA THAT FALLS BETWEEN THE TWO DATES. SO IS THE BEST APPROACH TO ADD A RNK AND SUBQUERY?
              )
WHERE 1=1
  AND RNK = 1 

Is there a easier way to extract what I'm asking?

End Results should be:

ID     Reason
1      Approved

Answer 1

One way is to use the FIRST_VALUE window function.

SELECT DISTINCT A.ID, FIRST_VALUE(A.REASON) OVER (PARTITION BY A.ID ORDER BY A.SET_DATE DESC) AS Reason
FROM TableB B
INNER JOIN TABLEA A ON B.ID = A.ID 
AND A.SET_DATE BETWEEN B.STARTDATE AND B.ENDDATE