Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use a CTE to calculate running total instead of using the SSRS function? Expand / Collapse
Author
Message
Posted Saturday, August 17, 2013 11:16 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 745, Visits: 4,776
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;

Post #1485555
Posted Sunday, August 18, 2013 11:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485607
Posted Sunday, August 18, 2013 12:32 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 745, Visits: 4,776
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
Post #1485613
Posted Sunday, August 18, 2013 4:31 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 745, Visits: 4,776
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;

Post #1485623
Posted Sunday, August 18, 2013 7:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485633
Posted Monday, August 19, 2013 11:30 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 745, Visits: 4,776
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!
Post #1485921
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse