locked
Need Help with Cumulative Net Totals RRS feed

  • Question

  • I have a script that has columns similar to the following.

    Customer Month Year Product1_Installs Product1_Cumulative_Net Product1_Deinstalls Product2_Installs Product2_Cumulative_Net Product2_Deinstalls

    What I need to have happen is the Product?_Cumulative_Net column be the running cumulative net total for that customer. The pseudo formula would be similar to this, "Last Cumulative Net Total + This month's installs - this month's deinstalls (Per product, per customer)".

    So, given the columns above and just the first product column, it would work like this.

    Customer Month Year Product1_Installs Product1_Cumulative_Net Product1_Deinstalls Notes
    Customer1 1 2008 2 1 1 +2 this month (2), -1 uninstall (1)
    Customer1 2 2008 5 3 3 +1 from Jan (1), +5 this month (6), -3 uninstalls (3)
    Customer1 5 2008 11 14 0 3 from Feb (0), +11 this month (14), -0 uninstalls (14)
    Customer2 2 2008 7 1 6 7 this month (7), -6 uninstalls (1), new customer so seperate running total
    Customer2 3 2008 2 0 3 1 from Feb (1), +2 this month (3), -3 uninstalls (0)
    Customer2 1 2009 4 4 0 0 from Mar 08 (0), +4 this month (4), -0 uninstalls (4)

    Things to consider:

    • Totals must be per customer
    • Totals should be NET, meaning installs - uninstalls
    • There will not necessarily be an install each month, so cumulative is from last cumulative, not last month

    I'm in a big hurry on this, so any help would be greatly appreciated! I tried doing it in reporting services, which is ultimately where this data will end up, but it requires you to base the running total on a group, which means it rolls up the data by customer name, eliminating some of my data. If you can figure out how to fix that problem, that would be much easier!


    Nathon Dalton .NET Software Developer
    Tuesday, September 29, 2009 10:05 PM

Answers

  • Running totals are VERY difficult to do, at least efficiently, in SQL Server.  Itzik Ben-Gan just had a series of articles in SQL Server Magazine on this topic.  I recommend you check that out.  Also there is a massive and very informative thread on sqlservercentral.com too.  This thread here has a trick way to do this, but note that you REALLy have to be particular with this trick or you WILL get the wrong answers (and actually can regardless).  http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/fa337bbf-1721-44e3-8b79-e02431154df1
    SQL Server MVP
    • Marked as answer by Nathon Dalton Wednesday, September 30, 2009 11:42 PM
    Tuesday, September 29, 2009 10:40 PM

All replies

  • Running totals are VERY difficult to do, at least efficiently, in SQL Server.  Itzik Ben-Gan just had a series of articles in SQL Server Magazine on this topic.  I recommend you check that out.  Also there is a massive and very informative thread on sqlservercentral.com too.  This thread here has a trick way to do this, but note that you REALLy have to be particular with this trick or you WILL get the wrong answers (and actually can regardless).  http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/fa337bbf-1721-44e3-8b79-e02431154df1
    SQL Server MVP
    • Marked as answer by Nathon Dalton Wednesday, September 30, 2009 11:42 PM
    Tuesday, September 29, 2009 10:40 PM
  • See also http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&p=38243&hilit=running+total#p38243
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    Wednesday, September 30, 2009 3:04 AM
  • something like this. .  .  (don't quite understand how / where those numbers in () comes from . . .)

    declare @sample table
    (
        Customer     varchar(10),
        [Month]     int,
        [Year]        int,    
        Installs    int,
        Deinstalls    int
    )
    
    insert into @sample
    select 'Customer1', 1, 2008,  2, 1    union all
    select 'Customer1', 2, 2008,  5, 3    union all
    select 'Customer1', 5, 2008, 11, 0    union all
    select 'Customer2', 2, 2008,  7, 6    union all
    select 'Customer2', 3, 2008,  2, 3    union all
    select 'Customer2', 1, 2009,  4, 0
    
    ; with
    data
    as
    (
        select    Customer, [Month], [Year],
            Installs    = sum(Installs),
            Deinstalls    = sum(Deinstalls),
            row_no        = row_number () over (partition by Customer order by [Year], [Month])
        from    @sample
        group by Customer, [Year], [Month]
    )
    select    d.Customer, d.[Month], d.[Year],
        d.Installs,
        Cumulative_Net    = isnull(c.Cumulative_Net, 0) + d.Installs - d.Deinstalls,
        d.Deinstalls,
        Notes        = case     when     p.Customer is not null
                    then     convert(varchar(10), p.Installs - p.Deinstalls)
                    +     ' from '
                    +     left(datename(month, dateadd(month, p.[Month] - 1, 0)), 3) + ', '
                    else ''
                    end
                + convert(varchar(10), d.Installs) + ' this month' + ', '
                + '-' + convert(varchar(10), d.Deinstalls) + ' uninstall'
    from    data d
        left join data p    on    d.Customer    = p.Customer
                    and    d.row_no    = p.row_no + 1
        cross apply
        (
            select    Cumulative_Net     = sum(Installs - Deinstalls)
            from    data x
            where    x.Customer    = d.Customer
            and    x.row_no    < d.row_no
        ) c

    KH Tan
    Wednesday, September 30, 2009 4:16 AM
  • Well, it turns out that TheSQLGuru is right in post #2, this is an incredibly difficult task to perform. So, I finally broke down and used a cursor. I know, I know... don't hate me because I wasn't smart enough to figure it out without the use of a cursor. :( The sky might fall, Earth stop spinning, universe come to an end as we know it, etc.

    The problem I have now is that I have to ultimately get these results in reporting services, but I now have a SQL script with a temporary table and a cursor and in order to get reporting services to use it as a data source, it must be either a table, view or function. However, I'm having issues trying to get this script into any of them. Anyone have any ideas on alternatives to the following so that I can get it into something reporting services will consume?

    • Create Table XYZ
    • Insert Into XYZ Select col1,col2,... From OtherTable
    • Update Cursor
    • Select * From XYZ

    Any help would be greatly appreciated! Also, any form of getting this into SSRS will work. Possibly a stored proc called by a view? Although I don't know if that makes sense.


    Nathon Dalton .NET Software Developer
    Wednesday, September 30, 2009 10:25 PM
  • This can be done more efficiently by letting the cursor be the complete driver of the process.  Use it to do the inserts to the table while tracking previous/current value changes to keep running totals set properly.  That eliminates the looping update at least.  Minor savings overall but it will speed things a tad.
    SQL Server MVP
    Thursday, October 1, 2009 2:13 PM
  • the query i post does not meet your requirement ?
    KH Tan
    Thursday, October 1, 2009 2:47 PM
  • Meeting requirements and being optimal/performant are two VERY different things, especially in this case.  :-)
    SQL Server MVP
    Thursday, October 1, 2009 2:59 PM
  • LOL, you all performed admirably! :) There are no "losers" here. The proper term is "winning deficient".

    Naw, just joking. Thank you guys very much for your input. Yea, the cursors sure add some time, but wound up being the only way I could get it done. I also have a task to create another report very similar to this one, except with quarterly and yearly totals... I smell another cursor coming on... and it stinks! :)
    Nathon Dalton .NET Software Developer
    Monday, October 5, 2009 10:43 PM