title: Return value in a table based on nearest date in another SQL
body: <p>I apologize if the title is ambiguous, but I couldn't think of a better way to distill down my objective.</p>
  
  <p>I have two tables: <code>AcctProdHist ph</code> and <code>AcctBalHist bh</code> that I have aliased ph and bh, respectively. <code>AcctProdHist</code> has the product history for an account and its <code>productdate</code>. <code>AcctBalHist</code> contains a history of the account balance by date balance changed <code>balancedate</code>. </p>
  
  <p>I am trying to find the <code>balance</code> in <code>AcctBalHist</code> for the <code>balancedate</code> nearest the <code>productdate</code> in <code>AcctProdHist</code>. There are two situations:</p>
  
  <ul>
  <li>ProductDate is between 2 BalanceDates, so I need the balance for the lesser of the two dates</li>
  <li>ProductDate is less than the min(BalanceDate)</li>
  </ul>
  
  <p>Here is my code for combining both tables and getting a full balance history:</p>
  
  <pre><code>select
  ph.acctnbr, 
  ph.product,
  ph.productdate,
  bh.balancedate,
  bh.balance
  
  from AcctProdHist ph, AcctBalHist bh
  
  where ((ph.acctnbr = bh.acctnbr(+)))
  
  and ph.acctnbr in (12345,67890)
  </code></pre>
  
  <p>Here are the results:</p>
  
  <pre><code>ACCTNBR Product ProductDate BalanceDate Balance
  12345   BYBU    7/16/2018   8/1/2018    550
  12345   BYBU    7/16/2018   7/31/2018   510
  12345   BYBU    7/16/2018   7/12/2018   500
  12345   BYBU    7/16/2018   7/11/2018   460
  12345   BYBU    7/16/2018   7/2/2018    450
  67890   ABAU    7/20/2018   8/5/2018    103
  67890   ABAU    7/20/2018   8/1/2018    102
  67890   ABAU    7/20/2018   7/31/2018   101
  67890   ABAU    7/20/2018   7/22/2018   100
  </code></pre>
  
  <p>In this case, the <code>Balance</code> I want for <code>ACCTNBR</code> 12345 is 500. The <code>Balance</code> I want for <code>ACCTNBR</code> 67890 is 100.</p>
  
  <p>I'm guessing i'll have to do a UNION query as i'm working with two different situations, but I don't even know where to start.</p>
  
  <p>Thanks!</p>
  
  <p>EDIT: Adding Sample Data from the individual tables.</p>
  
  <p><code>AcctProdHist</code></p>
  
  <pre><code>ACCTNBR Product ProductDate InactiveDate
  12345   ATRU    5/1/2016    7/16/2018
  12345   BYBU    7/16/2018   
  67890   ABAU    7/20/2018   
  </code></pre>
  
  <p><code>AcctBalHist</code></p>
  
  <pre><code>ACCTNBR BalanceDate Balance
  12345   1/1/2018    225
  12345   2/6/2018    268
  12345   4/20/2018   315
  12345   6/1/2018    400
  12345   7/2/2018    450
  12345   7/11/2018   460
  12345   7/12/2018   500
  12345   7/31/2018   510
  12345   8/1/2018    550
  67890   7/22/2018   100
  67890   7/31/2018   101
  67890   8/1/2018    102
  </code></pre>