title: How to optimize the query using join with a subquery?
body: <p>I want to be able to list the sum of the st.balancechange, but for different s.type. It is taking the query about 7 minutes to run. I'm sure there is a better way to optimize it, but I'm not sure how. I can't use it as a sub query, because I need to return more than one result.</p>
  
  <pre><code>SELECT DISTINCT a.ACCOUNTNUMBER AS [Account Number]
      , s1.Deposit AS [Aggregate Share Balance]
      , c.Deposit AS [Aggregate Checking Balance]
  
  FROM dbo.ACCOUNT a
  
  JOIN dbo.SAVINGS s
      ON a.ACCOUNTNUMBER = s.PARENTACCOUNT
          AND a.ProcessDate = s.ProcessDate
  
  JOIN (SELECT a.ACCOUNTNUMBER
      , SUM(st.BALANCECHANGE) AS [Deposit]
  FROM dbo.ACCOUNT a
  JOIN dbo.SAVINGS s
      ON a.ACCOUNTNUMBER = s.PARENTACCOUNT
          AND a.ProcessDate = s.ProcessDate
  JOIN dbo.SAVINGSTRANSACTION st
      ON st.PARENTACCOUNT = s.PARENTACCOUNT
  WHERE a.ProcessDate = CONVERT(VARCHAR(8), DATEADD(DAY,-1, GETDATE()), 112)
      AND a.CLOSEDATE IS NULL
      AND s.CLOSEDATE IS NULL 
      AND st.ACTIONCODE = 'D'
      AND s.TYPE IN (0, 1, 2, 3, 4, 6, 100)
      GROUP BY a.ACCOUNTNUMBER) s1 ON s1.ACCOUNTNUMBER = a.ACCOUNTNUMBER
  
  LEFT JOIN (SELECT a.ACCOUNTNUMBER
      , SUM(st.BALANCECHANGE) AS [Deposit]
  FROM dbo.ACCOUNT a
  JOIN dbo.SAVINGS s
      ON a.ACCOUNTNUMBER = s.PARENTACCOUNT
          AND a.ProcessDate = s.ProcessDate
  JOIN dbo.SAVINGSTRANSACTION st
      ON st.PARENTACCOUNT = s.PARENTACCOUNT
  WHERE a.ProcessDate = CONVERT(VARCHAR(8), DATEADD(DAY,-1, GETDATE()), 112)
      AND a.CLOSEDATE IS NULL
      AND s.CLOSEDATE IS NULL 
      AND st.ACTIONCODE = 'D'
      AND s.TYPE IN (2, 100, 101, 102, 103, 104, 105, 106, 107, 108, 113, 150, 201)
      GROUP BY a.ACCOUNTNUMBER) c ON c.ACCOUNTNUMBER = a.ACCOUNTNUMBER
  
  WHERE a.ProcessDate = CONVERT(VARCHAR(8), dateadd(day,-1, getdate()), 112)
      AND a.CLOSEDATE IS NULL
      AND s.CLOSEDATE IS NULL 
  ORDER BY a.ACCOUNTNUMBER
  </code></pre>
  
  <p>Current results:</p>
  
  <pre><code>Account Number  Aggregate Share Balance   Aggregate Checking Balance
  0000001234           7383.58                      NULL
  0000001235           95856.83                   95856.83
  0000001236            123.27                     123.27
  0000001237             1.88                       NULL
  0000001238           14812.26                     NULL
  0000001239            15.00                       NULL
  0000001240            4.06                        NULL
  0000001241            6.42                        3.21
  0000001242           3705.03                    3705.03
  0000001243          976841.06                  1465261.59
  </code></pre>