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