Databases
StackOverflow2013
Postsdbo
POFinding similar pairs of data using SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFinding similar pairs of data using SQL
    primarykey
    data
    text
    <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> <p>Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USlorey
    1. USJay C
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFinding similar pairs of data using SQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFinding similar pairs of data using SQL
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POFinding similar pairs of data using SQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload