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

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

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

  • 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

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

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

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

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

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