locked
running total RRS feed

  • Question

  • can someone suggest me a way to calculate the running total..

    i want the weekly update of the running total, and i need to display the dues for the last 6 weeks

    week 1     890

    week 2     760

    ....

    i want the running total..

    Tuesday, March 8, 2011 6:34 AM

Answers

All replies

  • Following is very nice artical on Running total and covers all aspect of it.

    Pleaee have look

    http://www.sqlteam.com/article/calculating-running-totals

     


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
    Tuesday, March 8, 2011 6:47 AM
  • Tuesday, March 8, 2011 7:09 AM
  • DECLARE

     

    @WeeklyLoanerAnalysis TABLE (Overduetotal int,RunningTotal int)

     

    DECLARE

     

    @RunningTotal int

    DECLARE

     

    @Overduetotal int

    DECLARE

     

    @TotalOpenLoaner int

    DECLARE

     

    @TotalOverDue int

     

    SET

     

    @RunningTotal = 0

     

    INSERT

     

    INTO @WeeklyLoanerAnalysis a

    select

     

    count([Open/Close]) as [TotalOpenLoaner], count(case when [Return Date] < getdate() then [Open/Close] else NULL end) as [TotalOverDues]

    from

     

    dbo.T_NewLoanerMaster where [Open/Close] = 'Open'

    UPDATE

     

    @WeeklyLoanerAnalysis

    SET

     

    @RunningTotal = RunningTotal = @RunningTotal + count(a.[Open/Close])

    FROM

     

    @WeeklyLoanerAnalysis

    UPDATE

     

    @WeeklyLoanerAnalysis

    SET

     

    @Overduetotal = Overduetotal = @Overduetotal + count(case when a.[Return Date] < getdate() then a.[Open/Close] else NULL end)

    FROM

     

    @WeeklyLoanerAnalysis

    SELECT

     

    * FROM @WeeklyLoanerAnalysis

     

    what i want is the running total for openloaner and overdue..

    the data is in the tbl loanermaster

    Tuesday, March 8, 2011 8:18 AM
  • Hi,

    Kindly have a look at below code

    DECLARE @WeeklyLoanerAnalysis TABLE (weeknumber int, Overduetotal int,RunningTotal int)
    
    insert into @WeeklyLoanerAnalysis
    select 1,890,nULL
    union all
    select 2,760, NULL
    union all
    select 3,1010, NULL
    
    ;with CTE
    as
    (
    SELECT a.weeknumber,
        a.Overduetotal,
        SUM(b.Overduetotal) as RunningTotal
    FROM @WeeklyLoanerAnalysis a
    INNER JOIN @WeeklyLoanerAnalysis b
    ON (b.weeknumber <= a.weeknumber) 
    GROUP BY a.weeknumber,a.Overduetotal
    )
    
    update @WeeklyLoanerAnalysis 
    set RunningTotal = b.RunningTotal
    from @WeeklyLoanerAnalysis a
    inner join CTE b on a.weeknumber = b.weeknumber
    
    select * from @WeeklyLoanerAnalysis
    
    

    - Chintak (My Blog)

    Tuesday, March 8, 2011 8:36 AM
  • Hi,

    Littel more optimized code

    DECLARE @WeeklyLoanerAnalysis TABLE (weeknumber int, Overduetotal int,RunningTotal int)
    
    insert into @WeeklyLoanerAnalysis
    select 1,890,nULL
    union all
    select 2,760, NULL
    union all
    select 3,1010, NULL
    
    update @WeeklyLoanerAnalysis 
    set RunningTotal = b.RunningTotal
    from @WeeklyLoanerAnalysis a
    Cross apply 
    (SELECT SUM(b.Overduetotal) as RunningTotal
    FROM @WeeklyLoanerAnalysis b
    where (b.weeknumber <= a.weeknumber) 
    ) b
    
    select * from @WeeklyLoanerAnalysis
    

    - Chintak (My Blog)

    Tuesday, March 8, 2011 8:52 AM
  • hi chinthak, thanks for the response.

    but here i already have the data in a table called tblLoaner

    i need the running values of total due and total loaner, the condition for loaner is that the status shld be open

    and for overdue, status as open and the return date <getdate()

    i need the running totals of both therse.. can you guide me

    Tuesday, March 8, 2011 9:05 AM
  • i need the running values of total due and total loaner, the condition for loaner is that the status shld be open

    The following page has example for running total calculation. It was developed by Paul Nielsen of www.sqlserverbible.com :

    http://www.sqlusa.com/bestpractices2005/runningtotalusingcursor/

    Let us know if helpful.


    Kalman Toth, SQL Server & BI Training; SQLUSA.com
    • Marked as answer by Ai-hua Qiu Wednesday, March 16, 2011 9:35 AM
    Tuesday, March 8, 2011 1:44 PM