Friday, January 12, 2007 6:32 PM tonyrogerson

Update table with a running (cumulative) total example

Quick example I've just posted on the NNTP news groups.

It's an example of how to create a running (cumulative) total in both a SELECT and UPDATE statements..

declare @data table (

    some_date     datetime    not null,

    some_value    int         not null,

    running_total int         null  --  will populate later

)

insert @data ( some_date, some_value ) values( '1 jan 2006', 1 )

insert @data ( some_date, some_value ) values( '1 feb 2006', 1 )

insert @data ( some_date, some_value ) values( '1 mar 2006', 1 )

insert @data ( some_date, some_value ) values( '1 apr 2006', 1 )

insert @data ( some_date, some_value ) values( '1 may 2006', 1 )

insert @data ( some_date, some_value ) values( '1 jun 2006', 1 )

 

select some_date,

       actual = some_value,

       running_total = ( select sum( some_value )

                         from @data d2

                         where d2.some_date <= d1.some_date )

from @data d1

order by some_date

 

--  To update...

update d1

    set running_total = ( select sum( some_value )

                          from @data d2

                          where d2.some_date <= d1.some_date )

 

from @data d1

 

select *

from @data

order by some_date

 

 

Filed under:

Comments

# re: Update table with a running (cumulative) total example

Tuesday, January 30, 2007 12:05 AM by degert

Unfortunately the query-plan that results from the "set-based" formulation is non-optimal... somewhere between approx (1/2)n^2 and n^2 rows are visited (depending on indexes).

Compare the performance of the "set-based" method -vs- a single table-scan / update via a cursor(!) for any significant amount of rows... say 10,000 or so...