title: How to calculate running total (month to date) in SQL Server 2008
body: <p>I'm trying to calculate a month-to-date total using SQL Server 2008.  </p>
  
  <p>I'm trying to generate a month-to-date count at the level of activities and representatives. Here are the results I want to generate:  </p>
  
  <pre><code>| REPRESENTATIVE_ID | MONTH | WEEK | TOTAL_WEEK_ACTIVITY_COUNT | MONTH_TO_DATE_ACTIVITIES_COUNT |
  |-------------------|-------|------|---------------------------|--------------------------------|
  |                40 |     7 | 7/08 |                         1 |                              1 |
  |                40 |     8 | 8/09 |                         1 |                              1 |
  |                40 |     8 | 8/10 |                         1 |                              2 |
  |                41 |     7 | 7/08 |                         2 |                              2 |
  |                41 |     8 | 8/08 |                         4 |                              4 |
  |                41 |     8 | 8/09 |                         3 |                              7 |
  |                41 |     8 | 8/10 |                         1 |                              8 |
  </code></pre>
  
  <p>From the following tables:  </p>
  
  <p><strong>ACTIVITIES_FACT table</strong>  </p>
  
  <pre><code>+-------------------+------+-----------+  
  | Representative_ID | Date | Activity  |  
  +-------------------+------+-----------+  
  |                41 | 8/03 | Call      |  
  |                41 | 8/04 | Call      |  
  |                41 | 8/05 | Call      |  
  +-------------------+------+-----------+  
  </code></pre>
  
  <p><strong>LU_TIME table</strong>  </p>
  
  <pre><code>+-------+-----------------+--------+  
  | Month | Date            | Week   |  
  +-------+-----------------+--------+  
  |     8 | 8/01            | 8/08   |  
  |     8 | 8/02            | 8/08   |  
  |     8 | 8/03            | 8/08   |  
  |     8 | 8/04            | 8/08   |  
  |     8 | 8/05            | 8/08   |  
  +-------+-----------------+--------+ 
  </code></pre>
  
  <p>I'm not sure how to do this: I keep running into problems with multiple-counting or aggregations not being allowed in subqueries.</p>