title: `LAST_VALUE` Between two Date
body: <p>I have the below table </p>
  
  <p>TableA</p>
  
  <pre><code>ASSIGNMENT_DT    START_DATE    END_DATE    CLT_ID  C_ID First_Service_Date FIRST_SERVICE_DATE2
      2/13/15       1/26/18         1/29/18     1      1                     1/30/18
      3/14/16       1/26/18         1/29/18     1      1                     1/30/18
      1/30/18       1/26/18         1/29/18     1      1                       NULL
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>,CASE WHEN FIRST_SERVICE_DATE IS NOT NULL THEN FIRST_SERVICE_DATE
    WHEN FIRST_SERVICE_DATE IS NULL AND ASSIGNMENT_DT &gt; START_DATE THEN NULL
    WHEN FIRST_SERVICE_DATE IS NULL AND ASSIGNMENT_DT &lt; START_DATE THEN LAST_VALUE(TRUNC(ASSIGNMENT_DT)) OVER (PARTITION BY CLT_ID
     ORDER BY CLIENTID ,ASSIGNMENT_DT RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  
  ELSE TO_DATE('9999/01/01', 'yyyy/mm/dd')
  
  END FIRST_SERVICE_DATE2
  </code></pre>
  
  <p>What I need:
   The First_Service_Date2 column should have one Agg value per Clt_id. 
   So for the example, When End_Date is Not null take the previous assignment_dt that is less than the start date</p>
  
  <p>When end_date is not null that the LEAD assignment date after the start_Date. </p>
  
  <p>Should be</p>
  
  <pre><code>First_Service_Date
     3/14/16
  </code></pre>