February 19, 2012 at 10:34 pm
I have a simple table
CREATE TABLE EnrollmentGoal(
ProtocolNo varchar(30),
WeekNumber int,
Goal int,
Actual int,
PRIMARY KEY (ProtocolNo, WeekNumber))
Here's some fake data...
INSERT INTO [SCRIDB].[dbo].[EnrollmentGoal]
([ProtocolNo]
,[WeekNumber]
,[Goal]
,[Actual])
VALUES
('LUN90'
,1
,10
,5);
INSERT INTO [SCRIDB].[dbo].[EnrollmentGoal]
([ProtocolNo]
,[WeekNumber]
,[Goal]
,[Actual])
VALUES
('LUN90'
,2
,10
,15);
INSERT INTO [SCRIDB].[dbo].[EnrollmentGoal]
([ProtocolNo]
,[WeekNumber]
,[Goal]
,[Actual])
VALUES
('LUN90'
,3
,10
,5);
INSERT INTO [SCRIDB].[dbo].[EnrollmentGoal]
([ProtocolNo]
,[WeekNumber]
,[Goal]
,[Actual])
VALUES
('LUN90'
,4
,10
,5);
GO
What I wanted to do was to graph the cumulative goal vs actual over time... (Basically a line chart with two lines charting totals over time.) I can create running totals just fine of Goal, Actual and the difference between them (if I calculate the weekly difference in the dataset).
What I wanted was something like a graph of a running total (so the slope of the graph os always positive or zero). One line would be the "cumulative Goal" and the other the "cumulative Actual" - essentially both running sums. I can get the running sums to work, but I cannot chart the running sums. Is it possible to do this?
Maybe I'll be less brain dead in the morning and it will become embarassingly obvious...
Thanks!
Pieter
February 19, 2012 at 11:14 pm
Answered my own question... ("Re-search. It means 'look again', don't it?") After a few minutes of surfing, I found this: http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum, which I basically tweaked for my own purposes to generate the cumulative totals in a query, and then based my graph on that.
Worked a champ! Well, thanks for listening...
February 19, 2012 at 11:50 pm
pietlinden (2/19/2012)
Answered my own question... ("Re-search. It means 'look again', don't it?") After a few minutes of surfing, I found this: http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum, which I basically tweaked for my own purposes to generate the cumulative totals in a query, and then based my graph on that.Worked a champ! Well, thanks for listening...
Thanks for coming back and posting the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 25, 2012 at 12:51 pm
Hopefully this helps someone else...
I got Itzik Ben-Gan's book Inside MS SQL Server 2008 T-SQL Querying, and basically modified one of his SQL queries to suit my problem. (I guess that's the definition of a great reference book - you can find something to fix your problem, and understand what the author is talking about in a few minutes!).
FWIW, here's the query I ended up using...
-- get total of "previous" records.
SELECT ie.ProtocolNo, ie.WeekNumber, ie.Goal, ie.Actual, ie.YTD_Goal, ie.YTD_Actual
FROM (
-- this is where the magic happens...
SELECT e1.ProtocolNo, e1.WeekNumber, e1.Goal, SUM(e2.Goal) AS YTD_Goal, e1.Actual, SUM(e2.Actual) AS YTD_Actual
FROM EnrollmentGoal AS e1 -- e1 and e2 point at the same table
JOIN EnrollmentGoal AS e2
ON e1.ProtocolNo = e2.ProtocolNo -- protocols are the same
AND e2.WeekNumber <=e1.weeknumber -- but the weeks are staggered
GROUP BY e1.ProtocolNo, e1.WeekNumber,e1.goal, e1.Actual -- so the totals accumulate differently
) as ie;
the cool part is the two pointers to the same dataset.... never thought of that!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply