locked
SQL Server 2005 - Running total RRS feed

  • Question

  • Hi All

    I have a fairly simple query which outputs a sum grouped by 3 items

    in SQL 2005 I cannot find a way to do a running subtotal - I want the count to reset whenever it finds a new category or year

    Select
    MSReportingCatID,
    YearOfAccount, 
    --(2016-YearOfAccount)*12+3 as DevMonth,
    Count(*) as trancount,
    convert(int,devmonthid) as devmonthid,
    --Sum(GrossPremiumMovt) as GrossPremMovt, 
    REPLACE(Convert(Varchar,Cast(sum(Grosspremiummovt) as Money),1), '.00','') as thesum 
    FROM dbo.vwFncMisUrepStatsMov
    where
    convert(int,devmonthid) in(1,2) AND
    yearofaccount > 1992 AND
    msreportingcatid = 1 and 
    settlementcurrency = 'GBP'
    Group by MSReportingCatID, YearofAccount, Convert(int,devmonthid)
    Order by YearOfAccount, Convert(int,devmonthid) asc

    Please could someone assist me in this end, I would prefer a non cursor method if possible

    Thanks alot


    J

    Thursday, April 21, 2016 11:58 AM

Answers

  • Try this:

    DECLARE @table TABLE (msReportingCatID INT, yearOfAccount INT, tranCount INT, devmonthID INT, grosPremMovt DECIMAL(10,2))
    INSERT INTO @table (msReportingCatID, yearOfAccount, tranCount, devmonthID, grosPremMovt) VALUES
    (1, 1993, 11, 1, 10500),     --10500
    (1, 1993, 65, 2, 212608.92), --223108.92
    (1, 1994, 16, 1, 5534.27),   --5534.27
    (1, 1994, 17, 2, 42391.42),  --47925.69
    (1, 1995, 5 , 1, 749.98),    --749.98
    (1, 1999, 8 , 2, 519.23)     --519.29
    
    SELECT t1.msReportingCatID, t1.yearOfAccount, t1.tranCount, t1.devmonthID, t1.grosPremMovt, 
           CONVERT(VARCHAR,CAST(t1.grosPremMovt AS MONEY),1) AS theSum,
           SUM(t2.grosPremMovt) AS runningTotal
      FROM @table t1
        INNER JOIN @table t2
    	  ON t1.msReportingCatID = t2.msReportingCatID
    	  AND t1.yearOfAccount = t2.yearOfAccount
    	  AND t1.devmonthID >= t2.devmonthID
     GROUP BY t1.msReportingCatID, t1.yearOfAccount, t1.tranCount, t1.devmonthID, t1.grosPremMovt

    Just use your existing query as a CTE.

    Typically when you ask a question like this, you should provide DDL and example data as I have in this example. This helps us to produce a working example using your own data.

    You want to avoid cursors in favor of a set based operation as much as possible. Think of cursors as REALLY big nails. If you can find a more graceful way to hang that picture, you'll be better off. Sometimes, you do need that really big nail, though.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by James OHara Thursday, April 21, 2016 3:37 PM
    Thursday, April 21, 2016 2:30 PM

All replies

  • The running totals column would be something like:

    (SELECT SUM(GrossPremiumMovt)
     FROM   bbo.vwFncMisUrepStatsMov b
     WHERE  b.yearofaccount = b.yearofaccount
       AND  b.msreportingcaidid = b.msreportingcatid
       AND  Convert(int, b.devmonthid) <= Convert(int,a.devmonthid)) AS running_sum

    I guess from the column name that there are only 12 values in each partition. Then this might work. To wit, the problem with the above is that the subquery is computed for every row, which means that performance is proportional to the square of the number of rows.

    For this reason, an iterative solution is often required to implement running sums on SQL 2008 and earlier.

    Starting with SQL 2012, you can use window aggregates with which performance is linear.

    Thursday, April 21, 2016 12:17 PM
  •  with cte as (
     Select
    MSReportingCatID,
    YearOfAccount, 
    (2016-YearOfAccount)*12+3 as DevMonth,
    Count(*) as trancount,
    convert(int,devmonthid) as devmonthid,
    Sum(GrossPremiumMovt) as GrossPremMovt, 
    REPLACE(Convert(Varchar,Cast(sum(Grosspremiummovt) as Money),1), '.00','') as thesum 
    FROM dbo.vwFncMisUrepStatsMov
    where
    convert(int,devmonthid) in(1,2) AND
    yearofaccount > 1992 AND
    msreportingcatid = 1 and 
    settlementcurrency = 'GBP'
    Group by MSReportingCatID, YearofAccount, Convert(int,devmonthid)
    Order by YearOfAccount, Convert(int,devmonthid) asc
    )
    
     select #1.MSReportingCatID,#1.YearOfAccount,#1.Trancount,#1.devmonthid,#1.GrossPremMovt,#1.thesum, 
     #1.thesum+isnull(#2.thesum,0) from cte #1 full join cte #2 
     on #1.yearofaccount=#2.yearofaccount and #1.devmonthid=#2.devmonthid+1
     where #1.MSReportingCatID is not null

    Please mark as answer if this post helped you
    Thursday, April 21, 2016 12:18 PM
  • Hi There

    Thanks for the fast reply, that appeared to simply replicate the first SUM row. unfortunately the devmonthid is iterative and can go all the way back to 280 (and rising) - 1 being the newest month. everything gets cleared out and reloaded every month and all the figures shuffle back one position

    Have you any other way of accomplishing the running tot?

    Thursday, April 21, 2016 12:30 PM
  • Anyway in order to have a subtotal you need to find a sequential logic somewhere...
    Thursday, April 21, 2016 12:42 PM
  • So if I wanted to do this WITH a cursor, is that possible?
    Thursday, April 21, 2016 1:08 PM
  • Yes...I don't like cursor but even using a cursor you need a logical sequence..you can use even a simple loop updating the table but again, where is the sequential logic?
    Thursday, April 21, 2016 1:16 PM
  • The logical sequence is that for every year of account - I need a running subtotal ordered by the devmonthid as shown in my screenshot. when it reaches a new Year of account OR Reportingcategory ID I want it to stop the subtotal and start again

    So example; for 1993 there are 280 devmonths - you only see 2 because I restricted it to devmonth 1 + 2 in my screenshot.

    I want it to subtotal category 1 - 1-280 devmonths for 1993, then reset to the next year - category 1 - 1994 and so on and so on!

    the logic is there

    Thursday, April 21, 2016 1:26 PM
  • Hi Eric

    How could I put this into a iterative cursor? any guidance would be appreciated

    Your first query gave me the correct answer set I was looking for but is very slow - reading up on it a cursor is the way to go here.

    WITH MYDATA AS(
    Select top 100 percent
    MSReportingCatID,
    YearOfAccount, 
    Count(*) as trancount,
    convert(int,devmonthid) as devmonthid,
    Sum(GrossPremiumMovt) as GrossPremMovt
    FROM dbo.vwFncMisUrepStatsMov
    where
    --convert(int,devmonthid) in(1,2) 
    --AND
    yearofaccount = 1993 
    AND
    msreportingcatid = 1 
    AND 
    settlementcurrency = 'GBP'
    Group by MSReportingCatID, YearofAccount, Convert(int,devmonthid)
    )
    
    Select M.*, 
    (Select REPLACE(Convert(Varchar,Cast(sum(Grosspremmovt) as Money),1), '.00','') as GrossPremMovt From MyData b WHERE b.yearofaccount = m.yearofaccount and b.msreportingcatid = m.msreportingcatid and convert(int,b.devmonthid) <= convert(int,m.devmonthid)) As RunningTot 
    FROM MyData M
    order by YearOfAccount, Convert(int,DevMonthID)

    Please could you help me convert this to a cursor?

    Thanks


    • Edited by James OHara Thursday, April 21, 2016 1:55 PM Update
    Thursday, April 21, 2016 1:33 PM
  • with cte as (
     Select
    MSReportingCatID,
    YearOfAccount, 
    (2016-YearOfAccount)*12+3 as DevMonth,
    Count(*) as trancount,
    convert(int,devmonthid) as devmonthid,
    Sum(GrossPremiumMovt) as GrossPremMovt, 
    REPLACE(Convert(Varchar,Cast(sum(Grosspremiummovt) as Money),1), '.00','') as thesum, 
    row_number() over (partition by YearOfAccount order by convert(int,devmonthid) as lnk
    FROM dbo.vwFncMisUrepStatsMov
    where
    convert(int,devmonthid) in(1,2) AND
    yearofaccount > 1992 AND
    msreportingcatid = 1 and 
    settlementcurrency = 'GBP'
    Group by MSReportingCatID, YearofAccount, Convert(int,devmonthid)
    Order by YearOfAccount, Convert(int,devmonthid) asc
    )
    
     select #1.MSReportingCatID,#1.YearOfAccount,#1.Trancount,#1.devmonthid,#1.GrossPremMovt,#1.thesum, 
     #1.thesum+isnull(#2.thesum,0) from cte #1 full join cte #2 
     on #1.yearofaccount=#2.yearofaccount and #1.lnk=#2.lnk+1
     where #1.MSReportingCatID is not null

    Thursday, April 21, 2016 2:22 PM
  • Try this:

    DECLARE @table TABLE (msReportingCatID INT, yearOfAccount INT, tranCount INT, devmonthID INT, grosPremMovt DECIMAL(10,2))
    INSERT INTO @table (msReportingCatID, yearOfAccount, tranCount, devmonthID, grosPremMovt) VALUES
    (1, 1993, 11, 1, 10500),     --10500
    (1, 1993, 65, 2, 212608.92), --223108.92
    (1, 1994, 16, 1, 5534.27),   --5534.27
    (1, 1994, 17, 2, 42391.42),  --47925.69
    (1, 1995, 5 , 1, 749.98),    --749.98
    (1, 1999, 8 , 2, 519.23)     --519.29
    
    SELECT t1.msReportingCatID, t1.yearOfAccount, t1.tranCount, t1.devmonthID, t1.grosPremMovt, 
           CONVERT(VARCHAR,CAST(t1.grosPremMovt AS MONEY),1) AS theSum,
           SUM(t2.grosPremMovt) AS runningTotal
      FROM @table t1
        INNER JOIN @table t2
    	  ON t1.msReportingCatID = t2.msReportingCatID
    	  AND t1.yearOfAccount = t2.yearOfAccount
    	  AND t1.devmonthID >= t2.devmonthID
     GROUP BY t1.msReportingCatID, t1.yearOfAccount, t1.tranCount, t1.devmonthID, t1.grosPremMovt

    Just use your existing query as a CTE.

    Typically when you ask a question like this, you should provide DDL and example data as I have in this example. This helps us to produce a working example using your own data.

    You want to avoid cursors in favor of a set based operation as much as possible. Think of cursors as REALLY big nails. If you can find a more graceful way to hang that picture, you'll be better off. Sometimes, you do need that really big nail, though.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by James OHara Thursday, April 21, 2016 3:37 PM
    Thursday, April 21, 2016 2:30 PM
  • Hi Patrick

    This is genius actually! learning a lot here

    The final query was:

    
    DECLARE @table TABLE (msReportingCatID INT, yearOfAccount INT, tranCount INT, devmonthID INT, grossPremMovt DECIMAL(10,2))
    INSERT INTO @table (msReportingCatID, yearOfAccount, tranCount, devmonthID, grossPremMovt)
    Select
    MSReportingCatID,
    YearOfAccount, 
    Count(*) as trancount,
    convert(int,devmonthid) as devmonthid,
    Sum(GrossPremiumMovt) as GrossPremMovt
    FROM dbo.vwFncMisUrepStatsMov
    where
    convert(int,devmonthid) in(1,2,3)
    --AND
    --yearofaccount = 1994
    AND
    msreportingcatid = 1 
    AND 
    settlementcurrency = 'GBP'
    Group by MSReportingCatID, YearofAccount, Convert(int,devmonthid)
    Order by MSReportingCatID, YearOfAccount, DevMonthID
    
    
    SELECT t1.msReportingCatID, t1.yearOfAccount, t1.tranCount, t1.devmonthID, t1.grossPremMovt, 
           CONVERT(VARCHAR,CAST(t1.grossPremMovt AS MONEY),1) AS theSum,
           SUM(t2.grossPremMovt) AS runningTotal
      FROM @table t1
        INNER JOIN @table t2
    	  ON t1.msReportingCatID = t2.msReportingCatID
    	  AND t1.yearOfAccount = t2.yearOfAccount
    	  AND t1.devmonthID >= t2.devmonthID
     GROUP BY t1.msReportingCatID, t1.yearOfAccount, t1.tranCount, t1.devmonthID, t1.grossPremMovt
     ORDER BY t1.msReportingCatID, T1.YearOfAccount, T1.DevMonthID
    

    This worked perfectly and my result set now comes back in a mere 8 seconds instead of 4m11! excellent answer - ratings

    Thankyou to everyone else who contributed also!

    Rgds


    J

    Thursday, April 21, 2016 3:38 PM