Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use a CTE to calculate running total instead of using the SSRS function?


Use a CTE to calculate running total instead of using the SSRS function?

Author
Message
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12503
I am trying to show a line graph of RunningSum(Goal) vs RunningSum(Actual) over time.

Here's the query I'm using as the basis of my report (which may be the issue):

SELECT        EnrollmentGoal.ProtocolNo, EnrollmentGoal.WeekNumber, EnrollmentGoal.Goal, COUNT(Enroll.enrollmentID) AS EnrollCount
FROM Protocol INNER JOIN
EnrollmentGoal ON Protocol.ProtocolNo = EnrollmentGoal.ProtocolNo LEFT OUTER JOIN
Enroll ON EnrollmentGoal.ProtocolNo = Enroll.e_ProtocolNo AND EnrollmentGoal.WeekNumber = Enroll.PWeek
GROUP BY EnrollmentGoal.ProtocolNo, EnrollmentGoal.WeekNumber, EnrollmentGoal.Goal



Is my best bet to create a CTE to calculate the running total and then base the graph on a dataset that runs that stored procedure? (starting to think so).

The problem is that although I can calculate the running total in SSRS, it does not appear to be a column that I can actually use in my graph.
Would I be better off using a CTE as the basis of my report and doing the running sum there?

FWIW, I tried this... and it's close, but I wanted the renumbering to restart after the ProtocolNo changes:
-- this is close, but I wanted to break on e_ProtocolNo
SELECT TOP 150 enrollmentID, e_ProtocolNo, PWeek,
RunningTotal = COUNT(enrollmentID) OVER (ORDER BY e_ProtocolNo, PWeek ROWS UNBOUNDED PRECEDING)
FROM enroll
WHERE e_ProtocolNo != 'BRE150'
ORDER BY e_ProtocolNo, PWeek;


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
If you have 2012, then there's some "new" functionality in the SUM() OVER fuction that will do this nicely for you.

If you have 2008 or 2005, then a recursive CTE (rCTE) would certainly solve the problem... kind of like a WHILE loop or cursor solves the problem. Still, that's probably ok for the normally small number of rows that are in such reports.

I'd recommend steering clear of "Triangular Joins" even if there are very few rows. See the following link for why.
Hidden RBAR: Triangular Joins

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12503
Easy button! <CLICK!> Thanks, Jeff!

For other poor noobs like me, here's at least the first part of the running total.

SELECT ProtocolNo
, WeekNumber
, Goal
, SUM(Goal) OVER (PARTITION BY ProtocolNo ORDER BY WeekNumber) AS CumulativeTotal
FROM EnrollmentGoal;



Now to add in the running total for Actual and then compare....

Pieter
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12503
Getting closer, I think.... really should have heeded my professor's advice and used a smaller dataset, but anyway! I think this is right


SELECT ProtocolNo
, WeekNumber
, Goal
-- ADD RTGoal here
, SUM(GOAL) OVER (PARTITION BY ProtocolNo
ORDER BY WeekNumber
ROWS UNBOUNDED PRECEDING) AS CumulativeGoal
, NewEnrolls
-- ADD RTEnrolls here
, SUM(NewEnrolls) OVER (PARTITION BY ProtocolNo ORDER BY WeekNumber) AS CumulativeEnrolls
, NewEnrolls - Goal AS OverUnder
FROM
(
SELECT ProtocolNo
, eg.WeekNumber
, Goal
, COUNT(e_PatientID) AS NewEnrolls
FROM EnrollmentGoal eg
LEFT JOIN Enroll e ON eg.ProtocolNo = e.e_ProtocolNo
AND e.PWeek = eg.WeekNumber
GROUP BY ProtocolNo, eg.WeekNumber, Goal
) x;


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
pietlinden (8/18/2013)
Getting closer, I think.... really should have heeded my professor's advice and used a smaller dataset, but anyway! I think this is right


SELECT ProtocolNo
, WeekNumber
, Goal
-- ADD RTGoal here
, SUM(GOAL) OVER (PARTITION BY ProtocolNo
ORDER BY WeekNumber
ROWS UNBOUNDED PRECEDING) AS CumulativeGoal
, NewEnrolls
-- ADD RTEnrolls here
, SUM(NewEnrolls) OVER (PARTITION BY ProtocolNo ORDER BY WeekNumber) AS CumulativeEnrolls
, NewEnrolls - Goal AS OverUnder
FROM
(
SELECT ProtocolNo
, eg.WeekNumber
, Goal
, COUNT(e_PatientID) AS NewEnrolls
FROM EnrollmentGoal eg
LEFT JOIN Enroll e ON eg.ProtocolNo = e.e_ProtocolNo
AND e.PWeek = eg.WeekNumber
GROUP BY ProtocolNo, eg.WeekNumber, Goal
) x;



Today's coffee is starting to wear off so I could be wrong but that doesn't look like a "running" total. It just looks like a weekly total. What version of Sql Server are you actually using?
EDIT: It was the coffee wearing off... I total missed the ROWS UNBOUNDED PRECEDING directive.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12503
LOL... I wish it only took coffee to wake me up.

The graph works. Might not be the best T-SQL to get me there, but it gets the job done. The whole reason I started down the CTE road was this:

My dataset acts kind of like a cheque register, so the "running total" stuff is significant. If I filter out some of the old records, my math goes sideways. As I see it, I have a couple of options: either use a trigger or calculated column to store the current running total, use a CTE to do it... or?

I know you're not a CTE fan, but is this a case for using one? If not, what are my other options?

Thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search