title: SQL Server Debit, Credit Query for imbalance
body: <p>I have a Transaction table that has id,postDate,account,debit,credit columns. </p>
  
  <p>In SQL Server I'm trying to write a union or join query that will highlight imbalances in this table. An imbalance is defined as any credit for an account that does not have a corresponding debit for another account. This table has millions of rows and some transactions are duplicates in it that need to be removed on a case by case basis, but I need a query that will highlight them.</p>
  
  <p>I started here with a union as a start. The finer points of how to highlight imbalances is the thing I need. I basically want an indicator on the row if the record is a duplicate. I'd also like to add a running balance column to the query as well...  If the approach I am taking is naive, it is because I am a novice at advanced joining. I have stubbed out for the column but hardcoded string data in it.</p>
  
  <pre><code> SELECT   id,
             b.account,
             b.credit,
             b.debit,
             b.duplicate 
     FROM   (SELECT t.id,
             t.account, 
             t.credit,
             t.debit,
             'false' as duplicate
             FROM   TransactionRegister t 
            UNION ALL 
            SELECT t.id, 
             t.account,
             t.debit,
             t.credit,
              'false' as duplicate
             FROM   TransactionRegister t ) AS b order by id desc
  </code></pre>
  
  <p>Here is a sample of the table or two transactions. Typically a debit transaction for one account will always have a matching credit to the another account. I just want to show credits without a matching debit and vice versa and let the user decide what to do or if it is legit. In this system it also appears that the matching transaction is always the next number in the sequence, but I cannot be totally sure that is 100% the case. Out of million records, if the user sees 100 that are not balancing, they can easily digest it.</p>
  
  <pre><code>  ID|Date|Account|Credit|Debit
    1|01/22/2018,22222,13500,0
    2|01/22/2018,11111,0,13500
  </code></pre>