Prev: rebuild reorg proc
Next: Fast SQL Server Stored Procedure runs slow in VB.NET
From: rodchar on 16 Apr 2010 16:59
Hi All,

rule:
T-SQL prior to SQL2005

Given this:
row, amt, run$
----------------
1,$15,NULL
2,$0,NULL
3,$10,NULL
4,$15,NULL


Transform to this:
row, amt, run$
----------------
1,15,$40
2,$0,$25
3,$10,$25
4,$15,$15

Basically, is there a way to populate the run$ col with a running total

Thanks,
rodchar


From: Tom Moreau on 16 Apr 2010 17:12
Try:

update m
set
[run$] = (select sum (m2.amt) from MyTable m2 where m2.[row] >= m.[row])
from
MyTable m

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"rodchar" <rodchar(a)discussions.microsoft.com> wrote in message
news:C1300156-42A9-4AE2-848D-F1F2BAF918BD(a)microsoft.com...
Hi All,

rule:
T-SQL prior to SQL2005

Given this:
row, amt, run$
----------------
1,$15,NULL
2,$0,NULL
3,$10,NULL
4,$15,NULL


Transform to this:
row, amt, run$
----------------
1,15,$40
2,$0,$25
3,$10,$25
4,$15,$15

Basically, is there a way to populate the run$ col with a running total

Thanks,
rodchar


From: rodchar on 16 Apr 2010 23:13
thanks that worked great,
rod.

"Tom Moreau" wrote:

> Try:
>
> update m
> set
> [run$] = (select sum (m2.amt) from MyTable m2 where m2.[row] >= m.[row])
> from
> MyTable m
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message
> news:C1300156-42A9-4AE2-848D-F1F2BAF918BD(a)microsoft.com...
> Hi All,
>
> rule:
> T-SQL prior to SQL2005
>
> Given this:
> row, amt, run$
> ----------------
> 1,$15,NULL
> 2,$0,NULL
> 3,$10,NULL
> 4,$15,NULL
>
>
> Transform to this:
> row, amt, run$
> ----------------
> 1,15,$40
> 2,$0,$25
> 3,$10,$25
> 4,$15,$15
>
> Basically, is there a way to populate the run$ col with a running total
>
> Thanks,
> rodchar
>
>
> .
>
 | 
Pages: 1
Prev: rebuild reorg proc
Next: Fast SQL Server Stored Procedure runs slow in VB.NET