Mar 28

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

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

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 (SELECT y.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: SQL,query,running total,views

blog comments powered by Disqus