title: SQL to calculate number of tasks open at a given time
body: <p>I have a nice query that calculates how many tasks have been opened and closed in a given week that works a charm, but I'm having difficulties extending it so that it can show me how many tasks we're still open at the end of that week.</p>
  
  <p>In my mind, the sql will need to count the total number of issues that have been opened from the beginning of time, up to that week and then do the same for the number of closed and subtract the two from each other but with my limited knowledge of SQL, I'm struggling how to write this as it falls outside of the group by clause.</p>
  
  <p>This is the SQL that I have:</p>
  
  <pre><code>SELECT  ISNULL(A.[Year],B.[Year]) [Year],
          ISNULL(A.[Week],B.[Week]) [Week],
          ISNULL(A.Opened,0) Opened,
          ISNULL(B.Closed,0) Closed,
  A.totResponse, A.totCompletion
  FROM (  SELECT  YEAR(insert_time) [Year],
                  DATEPART(WEEK,insert_time) [Week],
                  COUNT(id) Opened, sum(timer2) totResponse, sum(timer3) AS totCompletion
          FROM service_req
          WHERE [insert_time] IS NOT NULL and sr_type=1
          GROUP BY YEAR(insert_time), DATEPART(WEEK,insert_time)) A
  FULL JOIN ( SELECT  YEAR(close_time) [Year],
                      DATEPART(WEEK,close_time) [Week],
                      COUNT(id) Closed
              FROM service_req 
              WHERE [close_time] IS NOT NULL and sr_type=1
              GROUP BY YEAR(close_time), DATEPART(WEEK,close_time)) B
      ON A.[Year] = B.[Year] AND A.[Week] = B.[Week]
  ORDER BY [Year], [Week]
  </code></pre>
  
  <p>If there's anyone that could help me with this, that would be greatly appreciated.</p>