The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

SQL Running Totals

Mar28

Written by:
2009/03/28 09:44 PM RssIcon

Some time in your SQL life you are goimg to need running totals in order to produce a graph or report. There are many different ways toachive this. I had to do the running totals in  a SQL View, because the final view was being used by a report, which I had no control over.

I had to do some graphs for a client. Which was not too bad. I created a view in MS SQL database, which took me a while because my mind was totally focused on one way of trying to create the running total view. I then went to the MS forums and got some great ideas.

The graph was a total of things per process per month. So this worked, but it did not include the missing months. So I had to come up with a way to include counts of 0 for the missing month, have them included in the result set as rows so that I can graph them. What did I do?

I searched the News Groups and got some responses but eventually did a combination of suggestions.

First I created a list of joins for the months, like so:

SELECT'January' AS month, 0 AS IssueCount, 1 AS mnth
UNION
SELECT
'February' AS month, 0 AS IssueCount, 2 AS mnth
UNION
SELECT
'March' AS month, 0 AS IssueCount, 3 AS mnth
UNION
SELECT
'April' AS month, 0 AS IssueCount, 4 AS mnth
UNION
SELECT
'May' AS month, 0 AS IssueCount, 5 AS mnth
UNION
SELECT
'June' AS month, 0 AS IssueCount, 6 AS mnth
UNION
SELECT
'July' AS month, 0 AS IssueCount, 7 AS mnth
UNION
SELECT
'August' AS month, 0 AS IssueCount, 8 AS mnth
UNION
SELECT
'September' AS month, 0 AS IssueCount, 9 AS mnth
UNION
SELECT
'October' AS month, 0 AS IssueCount, 10 AS mnth
UNION
SELECT
'November' AS month, 0 AS IssueCount, 11 AS mnth
UNION
SELECT
'December' AS month, 0 AS IssueCount, 12 AS mnth

Which gave me a list of Months with at least a 0 count. I then added my initial query:

SELECT TOP (100) PERCENT DATENAME(mm, Issue.CreatedDate) AS Month
, COUNT(*) AS IssueCount, dbo.ActivityTask.ProcessId
FROMdbo.Issue AS Issue
INNER JOIN
dbo.ActivityTask ON Issue.ActivityTaskId = dbo.ActivityTask.ActivityTaskId
GROUP BY DATENAME(mm, Issue.CreatedDate)
,
MONTH(Issue.CreatedDate)
,
dbo.ActivityTask.ProcessId

and then did a cross join on this. The final query looked like this.

SELECT TOP (100) PERCENT p.ProcessId
, SUM(p.Issuecount) AS issuecount
, mnth
,
month
FROM (SELECTy.ProcessId, x.mnth
,
CASE WHEN y.month = x.month THEN y.issuecount ELSE 0 END AS Issuecount
,
CASE WHEN y.month = x.month THEN y.month ELSE x.month END AS
month
FROM (SELECT'January' AS month, 0 AS IssueCount, 1 AS mnth
UNION
SELECT
'February' AS month, 0 AS IssueCount, 2 AS mnth
UNION
SELECT
'March' AS month, 0 AS IssueCount, 3 AS mnth
UNION
SELECT
'April' AS month, 0 AS IssueCount, 4 AS mnth
UNION
SELECT
'May' AS month, 0 AS IssueCount, 5 AS mnth
UNION
SELECT
'June' AS month, 0 AS IssueCount, 6 AS mnth
UNION
SELECT
'July' AS month, 0 AS IssueCount, 7 AS mnth
UNION
SELECT
'August' AS month, 0 AS IssueCount, 8 AS mnth
UNION
SELECT
'September' AS month, 0 AS IssueCount, 9 AS mnth
UNION
SELECT
'October' AS month, 0 AS IssueCount, 10 AS mnth
UNION
SELECT
'November' AS month, 0 AS IssueCount, 11 AS mnth
UNION
SELECT
'December' AS month, 0 AS IssueCount, 12 AS mnth) AS x
CROSS JOIN
(
SELECT TOP (100) PERCENT DATENAME(mm, Issue.CreatedDate) AS Month
,
COUNT(*) AS IssueCount
,
dbo.ActivityTask.ProcessId
FROM dbo.Issue AS Issue
INNER JOIN
dbo.ActivityTask ON Issue.ActivityTaskId = dbo.ActivityTask.ActivityTaskId
GROUP BY DATENAME(mm, Issue.CreatedDate)
,
MONTH(Issue.CreatedDate)
,
dbo.ActivityTask.ProcessId) AS y) AS p
GROUP BY p.ProcessId, month, mnth
ORDER BY p.ProcessId, mnth

This worked great. It gave me totals per process id and per month. I was now able to graph this and then also include a 0 count if there was no processes identified in the database. But this was not enough. The client now wanted a second axis on the graph, showing a YTD figure. But not just the grand total of all the months per client, but a running total per client.

Now a running total is pretty much easy to implement. All you have to do is insert a sub-query as the field for the running total. Something like this using adventureworks:

select count(*) Customers ,( select count(*) as Running from Sales.Customer a where a.TerritoryID <= b.TerritoryID ) Running from Sales.Customer b group by TerritoryID

Customer Running
3433 3433
64 3497
71 3568
4581 8149
97 8246
1685 9931
1850 11781
1820 13601
3631 17232
1953 19185

Ok so that's a running total. But when you have multiple groups, you want the running total to reset after every group. Simple solution. Create a view with all the rows, then using the were clause you can make sure which groups need to be reset. So with the final query above (not the adventureworks one) in a view I created a second query utilizing the view and added my running total.

select *,
(
select top 1 sum(issuecount) from view_IssuecountByMonthProcess a
where a.processId = b.processid
and a.mnth <= b.mnth
) as YTD
from view_IssuecountByMonthProcess b
order by processid, mnth

This might not be the most elegant way of doing it. But it works great for me.

If you think you might have something to add. If you think you might have a better solution. Let me know. Leave a comment.

Now back to my website to do some business.

del.icio.us Tags: ,,,

Technorati Tags: ,,,

Tags:
Categories:
blog comments powered by Disqus

2 comment(s) so far...


Gravatar

Re: SQL Running Totals

Great stuff Robert.
If dBASE allowed for this type of a nested query, we would have it licked but dBASE don't so, another approach would need to be implimented.
As you know, in a loop it is nothing to have an accumulator and display it.
But it would be nice to have it wrapped up in one sql statement.

Greg Hill

By Greg on  2009/04/24 03:45 PM
Gravatar

Well , the view of the passage is totally correct ,your details is really reasonable and you guy give us valuable informative post, I totally agree the standpoint of upstairs. I often surfing on this forum when I m free and I find there are so much good

Well , the view of the passage is totally correct ,your details is really reasonable and you guy give us valuable informative post, I totally agree the standpoint of upstairs. I often surfing on this forum when I m free and I find there are so much good information we can learn in this forum! http://www.in-donesia.net/

By king on  2010/06/05 07:17 AM
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<June 2017>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print