How to calculate running total (month to date) in SQL Server 2008

I’m trying to calculate a month-to-date total using SQL Server 2008.

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:

| 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 |

From the following tables:

ACTIVITIES_FACT table

+-------------------+------+-----------+  
| Representative_ID | Date | Activity  |  
+-------------------+------+-----------+  
|                41 | 8/03 | Call      |  
|                41 | 8/04 | Call      |  
|                41 | 8/05 | Call      |  
+-------------------+------+-----------+  

LU_TIME table

+-------+-----------------+--------+  
| 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   |  
+-------+-----------------+--------+ 

I’m not sure how to do this: I keep running into problems with multiple-counting or aggregations not being allowed in subqueries.

Answer

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

I THINK He wants a running total for Month by each Representative_Id, so a simple group by week isn’t enough. He probably wants his Month_To_Date_Activities_Count to be updated at the end of every week.

This query gives a running total (month to end-of-week date) ordered by Representative_Id, Week

SELECT a.Representative_ID, l.month, l.Week, Count(*) AS Total_Week_Activity_Count
    ,(SELECT  count(*)
        FROM ACTIVITIES_FACT a2
        INNER JOIN LU_TIME l2 ON a2.Date = l2.Date
        AND a.Representative_ID = a2.Representative_ID
        WHERE l2.week <=  l.week
        AND l2.month = l.month) Month_To_Date_Activities_Count
FROM ACTIVITIES_FACT a
INNER JOIN LU_TIME l ON a.Date = l.Date
GROUP BY a.Representative_ID, l.Week, l.month
ORDER BY a.Representative_ID, l.Week

---

| 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 |

SQL Fiddle Sample