Running Totals in Stored Procedures

  • I have a stored procedure that utilizes a temp table to insert values from a cursor and then calculates a running total based on the records. My problem is, I need to group by at least two of the records and then have the running total re-zero itself for each group. In other words a running total for each group. Below is the script for the sp. This script keeps a running total for all groups based on the sort order. Not really what I am looking for. Any ideas would be appreciated.

    --CREATE procedure sp_ngst as

    Create table #ngst -- Build temp table

    (

    pointclassvarchar(32),

    pointvarchar(32),

    volumenumeric(15),

    maxstoragenumeric(15,4),

    minstoragenumeric(15,4),

    quantitytypevarchar(32),

    dividernumeric(5),

    timeunitvarchar(6),

    daysdatetime,

    RunningTotalnumeric(15)

    )

    Declare @pointclassvarchar(32), -- Declare all variables used in the cursor.

    @pointvarchar(32),

    @volumenumeric(15),

    @maxstoragenumeric(15,4),

    @minstoragenumeric(15,4),

    @quantitytypevarchar(32),

    @dividernumeric(5),

    @timeunitvarchar(6),

    @daysdatetime,

    @RunningTotalnumeric(15)

    Set @RunningTotal = 0 -- Set running total initially to zero.

    Declare a_cursor CURSOR -- Declare and open the base select statement using CASE to return calculated results.

    for

    Select isnull(ngp.pointclass,'None') pointclass, ngp.point,

    CASE

    When ngq.timeunit = 'MONTH' THEN ngq.volume/(datediff(dd, ngq.begtime, ngq.endtime))

    When ngq.timeunit = 'DAY' THEN ngq.volume*24

    When ngq.timeunit = 'TOTAL' and datediff(dd, ngq.begtime, ngq.endtime) > 0 THEN ngq.volume/(datediff(dd, ngq.begtime, ngq.endtime))

    Else ngq.volume

    End As volume

    ,ngp.maxstorage, ngp.minstorage, ngq.quantitytype, datediff(dd, ngq.begtime, ngq.endtime) AS divider, ngq.timeunit, tc.days

    from ngpoint ngp, ngposition ngptn, ngquantity ngq, temp_calendar tc

    where ngq.position = ngptn.position

    and ngq.seq = ngptn.seq

    and ngp.point = ngptn.point

    and tc.days >= ngq.begtime

    and tc.days <= ngq.endtime

    and ngq.posstatus = 'Y'

    order by ngp.point, tc.days

    Open a_cursor

    Fetch next from a_cursor into @pointclass, @point, @volume, @maxstorage, @minstorage, @quantitytype, @divider, @timeunit, @days

    While @@FETCH_STATUS = 0 -- Loop through the cursor and insert the variables into the temp table.

    Begin

    Set @RunningTotal = @RunningTotal + @volume

    Insert #ngst values (@pointclass, @point, @volume, @maxstorage, @minstorage, @quantitytype, @divider, @timeunit, @days, @RunningTotal)

    Fetch next from a_cursor into @pointclass, @point, @volume, @maxstorage, @minstorage, @quantitytype, @divider, @timeunit, @days

    End

    Close a_cursor

    Deallocate a_cursor

    Select * from #ngst -- Select the result of the insert.

    order by point

    compute sum(volume) by point

    drop table #ngst -- Drop the temp table and start over.

    GO

  • You are cross posting - the site owners get cross about that kind of thang.

    This is one way to create break totals and running totals without cursors or temp tables.

    
    
    set nocount on

    create table #running
    (keyval int identity(1,1) not null
    , breakval int not null
    , runval int not null)

    insert into #running (breakval, runval) values (1,1)
    insert into #running (breakval, runval) values (2,3)
    insert into #running (breakval, runval) values (3,2)
    insert into #running (breakval, runval) values (2,6)
    insert into #running (breakval, runval) values (3,5)
    insert into #running (breakval, runval) values (2,2)
    insert into #running (breakval, runval) values (1,1)
    insert into #running (breakval, runval) values (2,9)
    insert into #running (breakval, runval) values (1,3)

    set nocount off

    select
    r.breakval
    , (select sum(r1.runval) from #running r1 where r1.breakval=r.breakval) as break_total
    , (select sum(r2.runval) from #running r2 where r2.breakval<=r.breakval) as running_total
    from #running r
    group by r.breakval
    order by r.breakval asc

    drop table #running
    breakval    break_total running_total 
    
    ----------- ----------- -------------
    1 5 5
    2 20 25
    3 7 32
  • I realized that after I sent out the first posting. Sorry about that. I'm new to the site but will get the hange of things shortly. Thanks for all the input, it fixed my little problem.

Viewing 4 posts - 1 through 3 (of 3 total)

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