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

Question

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

pietlinden

SSC Guru

Points: 63503
More actions
August 17, 2013 at 11:16 pm

#276291

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;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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.

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 2

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 SSC Guru Points: 63503 More actions · Answer 3

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 SSC Guru Points: 1004356 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 5

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!