On Sep 12, 10:02 am, BGL <B...@discussions.microsoft.com> wrote:
> I was looking into ways to calculate running sums in TSQL. (I know, maybe it
> belongs in the app, and not in the DB). Self joins and correlated
> sub-queries in the SELECT list both work, but they are slow.
>
> Poking around the web I found this interesting UPDATE statement that is able
> to produce the running sum.
>
> Is this UPDATE legal? Is this a common construct? It syntactically works,
> but it stikes me as similar to the undocumented string concatenation behavior
> of string variables in TSQL which are discouraged from being used because
> they can be unpredictable.
>
> I'll be ripped a new one by Celko for counting hierarchy nodes like this,
> but maybe he can help? :-)
>
> if object_id('tempdb..#temp') is not null
> drop table #temp
> GO
>
> create table #temp (IndId int identity(1,1),NodeCnt int,RunningSum int,
> primary key clustered (IndId))
>
> insert #temp (NodeCnt,RunningSum)
> select 0,0 union all
> select 3,0 union all
> select 11,0 union all
> select 3,0 union all
> select 7,0
> GO
>
> declare @total int
> set @total = 0
>
> update #temp
> set @total = RunningSum = NodeCnt + @total
>
> select * from #temp
>
> IndId NodeCnt RunningSum
> ----------- ----------- -----------
> 1 0 0
> 2 3 3
> 3 11 14
> 4 3 17
> 5 7 24
>
> (5 row(s) affected)

"Shiju Samuel" wrote:

> I _think_ if you don't care about order in which the running total
> should be calculated this trick works.
>
> I tried doing an update on a ordered derived table. It doesn't work as
> I expected. update is still happening in the unordered set
>
>
> create table #temp (IndId int ,NodeCnt int,RunningSum int)
> --primary key clustered (IndId))
>
>
> insert #temp
> select 1,0,0 union all
> select 4,3,0 union all
> select 3,11,0 union all
> select 2,3,0 union all
> select 5,7,0
> GO
>
>
> declare @total int
> set @total = 0
> update a
> set @total = RunningSum = NodeCnt + @total
> from
> (select top 100 percent
> * from #temp
> order by indid) a
>
>
> -
> Shiju Samuel
>
>
> On Sep 12, 10:02 am, BGL <B...@discussions.microsoft.com> wrote:
> > I was looking into ways to calculate running sums in TSQL. (I know, maybe it
> > belongs in the app, and not in the DB). Self joins and correlated
> > sub-queries in the SELECT list both work, but they are slow.
> >
> > Poking around the web I found this interesting UPDATE statement that is able
> > to produce the running sum.
> >
> > Is this UPDATE legal? Is this a common construct? It syntactically works,
> > but it stikes me as similar to the undocumented string concatenation behavior
> > of string variables in TSQL which are discouraged from being used because
> > they can be unpredictable.
> >
> > I'll be ripped a new one by Celko for counting hierarchy nodes like this,
> > but maybe he can help? :-)
> >
> > if object_id('tempdb..#temp') is not null
> > drop table #temp
> > GO
> >
> > create table #temp (IndId int identity(1,1),NodeCnt int,RunningSum int,
> > primary key clustered (IndId))
> >
> > insert #temp (NodeCnt,RunningSum)
> > select 0,0 union all
> > select 3,0 union all
> > select 11,0 union all
> > select 3,0 union all
> > select 7,0
> > GO
> >
> > declare @total int
> > set @total = 0
> >
> > update #temp
> > set @total = RunningSum = NodeCnt + @total
> >
> > select * from #temp
> >
> > IndId NodeCnt RunningSum
> > ----------- ----------- -----------
> > 1 0 0
> > 2 3 3
> > 3 11 14
> > 4 3 17
> > 5 7 24
> >
> > (5 row(s) affected)
>
>