title: SQL Query to Join Two Tables Based On Closest Timestamp
body: <p>I need to retrieve the records from dbo.transaction (transaction of all users-more than one transaction for each user) that having timestamp which is closest to the time in  dbo.bal (current balance details of each user-only one record for each user)</p>
  
  <p>ie, the resultant records should equal to the no of records in the dbo.bal</p>
  
  <p>Here i tried the below query, am getting only the records less than the time in dbo.bal. But there are some record having timestamp greater than and closest to dbo.bal.time </p>
  
  <pre><code>SELECT dbo.bal.uid,
         dbo.bal.userId,
         dbo.bal.balance,
         dbo.bal.time,
    (SELECT TOP 1 transactionBal
     FROM dbo.transaction
     WHERE TIMESTAMP &lt;= dbo.bal.time
     ORDER BY TIMESTAMP DESC) AS newBal
  FROM dbo.bal
  WHERE dbo.bal.time IS NOT NULL
  ORDER BY dbo.bal.time DESC
  </code></pre>
  
  <p>here is my table structure,</p>
  
  <pre><code>dbo.transaction
  ---------------
  
  | uid| userId   | description| timestamp               | credit | transactionBal
  -------------------------------------------------------------------------
  | 1  | 101      | buy credit1| 2012-01-25 03:23:31.624 | 100    | 500
  | 2  | 102      | buy credit5| 2012-01-18 03:13:12.657 | 500    | 700
  | 3  | 103      | buy credit3| 2012-01-15 02:16:34.667 | 300    | 300
  | 4  | 101      | buy credit2| 2012-01-13 05:34:45.637 | 200    | 300
  | 5  | 101      | buy credit1| 2012-01-12 07:45:21.457 | 100    | 100
  | 6  | 102      | buy credit2| 2012-01-01 08:18:34.677 | 200    | 200
  
  dbo.bal
  -------
  
  | uid| userId   | balance | time                    |
  -----------------------------------------------------
  | 1  | 101      | 500     | 2012-01-13 05:34:45.645 |
  | 2  | 102      | 700     | 2012-01-01 08:18:34.685 |
  | 3  | 103      | 300     | 2012-01-15 02:16:34.672 |
  </code></pre>
  
  <p>And the result should be like,</p>
  
  <pre><code>| Id | userId   | balance | time                    | credit | transactionBal 
  -----------------------------------------------------------------------------
  | 1  | 101      | 500     | 2012-01-13 05:34:45.645 | 200    | 300
  | 2  | 102      | 700     | 2012-01-01 08:18:34.685 | 200    | 200
  | 3  | 103      | 300     | 2012-01-15 02:16:34.672 | 300    | 300
  </code></pre>
  
  <p>Please help me.. Any help is must appreciated...Thankyou</p>