I know there are several, close examples of this but none seem to do the trick for me. Essentially I am tracking weekly transactions of two types across several sites and would like to create a query that provides me with the total of monthly transactions for all sites and the running total of cumulative transactions for each transaction type.
I have 11 different sites for which I receive weekly totals of transactions that I want to do monthly running totals for. The query would produce the following:
MonthYear | TransactionsA | RunningTotalA | TransactionsB | Running TotalB
JUL-2011 10 10 15 15
Aug-2011 10 20 20 35
I am able to group sites up in months for the total transactions, but can't figure out a decent way to do a running total. The MS example found here does not allow for doing the total across years: http://support.microsoft.com/kb/290136/en-us
The snippet provided is the query I put together to do the total monthly transactions.
Thanks in advance,
SELECT Sum(usage.transactionA) AS TransactionA, Sum(usage.transactionB) AS TransactionB, Format$([usage].[weekly_period],'mmm-yy') AS [Month]
GROUP BY Format$([usage].[weekly_period],'mmm-yy'), Year([usage].[weekly_period])*12+DatePart('m',[usage].[weekly_period])-1
ORDER BY Year([usage].[weekly_period])*12+DatePart('m',[usage].[weekly_period])-1;