title: Finding similar pairs of data using SQL
body: <p>I have written the following Query</p>
  
  <p><strong>Sample Query</strong></p>
  
  <pre><code>USE SampleDB
  SELECT Sec.SecurityID, Port.PortfolioCode, Sec.SymbolNameSpace, Sec.Symbol, 
      PT.TradeAmount, PT.tradedate, PT.settledate, PT.PostDate, PT.TransactionCode, 
      PT.SecurityID1
  FROM AdvApp.vPortfolioTransaction PT
  LEFT OUTER JOIN
      AdvApp.vPortfolio PORT
      ON Port.PortfolioID = PT.PortfolioID
  LEFT OUTER JOIN
      AdvApp.vSecurity Sec
      ON Sec.SecurityID = PT.SecurityID1
  WHERE Sec.SecurityID = 4678 or Sec.SecurityID = 6
  ORDER BY PT.TradeAmount
  </code></pre>
  
  <p><strong>Desired Result Set</strong>                                  </p>
  
  <pre><code>SecurityID  PortfolioCode   SymbolNameSpace Symbol  TradeAmount tradedate   settledate  PostDate    TransactionCode SecurityID1
  4678    pendingtest caus    pending  368,456.00     5/21/2013   NULL    5/21/2013   lo  4678
  6   pendingtest caus    cash     368,456.15     5/22/2013   NULL    5/23/2013   lo  6
  4678    pendingtest caus    pending  7,800.00   4/17/2013   NULL    5/21/2013   lo  4678
  6   pendingtest caus    cash     7,801.00   4/23/2013   NULL    5/23/2013   lo  6
  </code></pre>
  
  <p><strong>Sample Actual Result Set</strong>                                    </p>
  
  <pre><code>SecurityID  PortfolioCode   SymbolNameSpace Symbol  TradeAmount tradedate   settledate  PostDate    TransactionCode SecurityID1
  6   pendingtest caus    cash     240,453.70     7/16/2010   NULL    7/19/2010   lo  6
  6   pendingtest caus    cash     249,562.32     1/19/2012   1/19/2012   1/20/2012   dp  6
  6   pendingtest caus    cash     368,456.15     5/22/2013   NULL    5/23/2013   lo  6
  6   pendingtest caus    cash     250,000.00     12/1/2003   NULL    12/1/2003   lo  6
  6   pendingtest caus    cash     250,321.13     11/15/2010  11/15/2010  11/16/2010  dp  6
  6   pendingtest caus    cash     365,445.58     1/31/1999   NULL    3/26/1999   dp  6
  4678    pendingtest caus    pending  368,456.00     5/21/2013   NULL    5/21/2013   lo  4678
  4678    pendingtest caus    pending  7,800.00   4/17/2013   NULL    5/21/2013   lo  4678
  6   pendingtest caus    cash     7,801.00   4/23/2013   NULL    5/23/2013   lo  6
  </code></pre>
  
  <p>The logic is I only want to see table rows where pairs of rows match tradeamounts within +/- 5. I also only want to see lines where the SecurityID1 value is a combination of 4678 and 6. The pair will always have one row being SecurityID1 4678 and the other row being SecurityID1 6.</p>
  
  <p>I searched extensively for how to pair transactions but I can't find any examples of how I would allow similar values within a tolerance of +/- 5</p>