Calculating running total of second field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculating running total of second field

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-03 : 11:43:06

Is it possible to calculate the running total of second field and populate in thrid field NewEVValue.

i created both values, you can see the second select to see the calculated running values populated in third field New EVValue.
How to acheive it with an update statement.

declare @Sample_first table (ActivityID int, EV_Value float, NewEVValue float)
insert @Sample_first
select 1, 12.00, 0 union all
select 2, 12.00, 0 union all
select 7, 12.00, 0 union all
select 22, 12.00, 0 union all
select 23, 12.00, 0 union all
select 45, 12.00, 0 union all
select 46, 12.00, 0

select * from @Sample_first

----should be this way after the calculation of third field running total based on second field EV_value

declare @Sample_Final table (ActivityID int, EV_Value float, NewEVValue float)
insert @Sample_Final
select 1, 12.00, 12.00 union all
select 2, 12.00, 24.00 union all
select 7, 12.00, 36.00 union all
select 22, 12.00, 48.00 union all
select 23, 12.00, 60.00 union all
select 45, 12.00, 72.00 union all
select 46, 12.00, 84.00

select * from @Sample_Final

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 12:18:20

[code]
UPDATE s
SET s.NewEVValue = s.EV_Value + COALESCE(s1.EV_Value,0)
FROM @Sample_Final s
OUTER APPLY (SELECT SUM(EV_Value)
FROM @Sample_Final
WHERE ActivityID < s.ActivityID ) s1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lewie
Starting Member

42 Posts

Posted - 2011-10-04 : 04:26:02

Or in a select statement

select ActivityID,EV_Value,EV_ValueSum from @Sample_first s Outer Apply (Select SUM(EV_Value) as EV_ValueSum
FROM @Sample_first
WHERE ActivityID <= s.ActivityID) s1

m_imran18
Starting Member

14 Posts

Posted - 2011-10-04 : 07:19:01

Update t2 set [newEVValue] =(select SUM(isnull(NewEVValue,0)+ISNULL(EV_Value,0)) as [new_Value]
from @Sample_first
where [ActivityID] <= t2.[ActivityID])
from @Sample_first as t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources