title: SQL query multiplying SUM results when joining to multiple transaction tables
body: <p>I'm developing a report which measures cash performance based on allocation and cancellation dates. To measure this I've queried a transactions sub query which pulls back all transactions and is joined on an account number and transaction from and to dates. This is then summed up at the top of the query.</p>
  
  <p>All works fine when using one sub query however I'm now having to add an extra transactions sub query; 1 to calculate actual payments from the allocation date (to cancellation date or today if value is null) and the 2nd to calculate any cancelled payments from the allocation date + 14 days (to cancellation date or today if value is null).</p>
  
  <p>The first sub query works perfectly but now I've added the second it's multiplying the summed value by 8. I've had this in the past when trying to query this table twice. (Not always by 8). Any ideas?</p>
  
  <p>Thanks </p>
  
  <p>The sums totalling these (TEST1 works fine, TEST2 not so much) and the sub queries:</p>
  
  <pre><code>SUM(CASE WHEN v1.[Transaction Date1] &gt;= dateadd(DD, 1, EOMONTH(getdate(), -1)) 
              THEN v1.[Payments] ELSE 0 
      END) AS TEST1,
  SUM(CASE WHEN v2.[Transaction Date2] &gt;= dateadd(DD, 1, EOMONTH(getdate(), -1)) 
              THEN v2.[Payments2] ELSE 0 
      END) AS TEST2,
  </code></pre>
  
  <p>My code:</p>
  
  <pre><code>LEFT JOIN 
      (SELECT 
           [Account Number1], [Transaction Date1], SUM([Payments1]) AS [Payments]
       FROM 
           (SELECT
                ACCOUNTNO AS [Account Number1],
                TRANDT AS [Transaction Date1],
                CASE 
                   WHEN TRANSACTIONTYPE = 'Credit' 
                      THEN TRANAMT * -1
                      ELSE TRANAMT
                END [Payments1]
            FROM 
                TRANSACTIONS t WITH (NOLOCK)
            JOIN 
                TRANSACTIONDETAILS td WITH (NOLOCK) ON td.TRANSACTIONID = t.TRANSACTIONS1
            JOIN 
                TRANSACTIONS a WITH (NOLOCK) ON a.ID = TRANSACTIONS1
                                             AND [TRANTYPE] LIKE '%PAY%'
                                             AND [TRANAMT] &lt;&gt; 0
            INNER JOIN 
                ACCOUNTS na WITH (NOLOCK) ON a.ACCOUNTID = ACCOUNTS1
            WHERE 
                TRANSACTIONTYPE = 'Credit') v
       GROUP BY 
           [Account Number1], [Transaction Date1]) v1 ON MAINT.[ACCNO] = v1.[Account Number1]
                                                      AND MAINT.[Allocation Date] &lt;= v1.[Transaction Date1]
                                                      AND v1.[Transaction Date1] &lt;=  MAINT.[Transaction to date]
  LEFT JOIN 
      (SELECT 
           [Account Number2], [Transaction Date2], SUM([Payments2]) AS [Payments2]
       FROM 
           (SELECT
                ACCOUNTNO AS [Account Number2],
                TRANDT AS [Transaction Date2],
                CASE  
                   WHEN TRANSACTIONTYPE = 'Credit' 
                      THEN TRANAMT * -1
                      ELSE TRANAMT
                END [Payments2]
            FROM 
                TRANSACTIONS t WITH (NOLOCK)
            INNER JOIN 
                TRANSACTIONDETAILS td WITH (NOLOCK) ON td.TRANSACTIONID = t.TRANSACTIONS1
            INNER JOIN 
                TRANSACTIONS a WITH (NOLOCK) ON a.ID = TRANSACTIONS1
                                             AND [TRANTYPE] LIKE '%PAY%'
                                             AND [TRANAMT] &lt;&gt; 0
            INNER JOIN 
                ACCOUNTS na WITH (NOLOCK) ON a.ACCOUNTID = ACCOUNTS1
            WHERE 
                TRANSACTIONTYPE = 'Debit') v
        GROUP BY 
            [Account Number2], [Transaction Date2]) v2 ON MAINT.[ACCNO] = v2.[Account Number2]
                                                       AND DATEADD(DD,10,MAINT.[DCA Allocation Date]) &lt;= v2.[Transaction Date2]
                                                       AND v2.[Transaction Date2] &lt;=  MAINT.[Transaction to date]
  </code></pre>