is it possible Need a running total based on date

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)

is it possible Need a running total based on date

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-23 : 12:41:12

How to add the earned value, order by inv_date to EarnedAsOfNow field.

As a running total based on date.

the first row earnedasofnow should be '126.34'
the second row based on dates should be 126.34 + 128.22 = '254.56'

Declare @Sample table (ID int, Earned float, EarnedAsOfNow float, Inv_date datetime)
insert @Sample
select 1, '126.34', '0', '12/10/2009' union all
select 2, '115.12', '0', '01/10/2010' union all
select 3, '128.22', '0', '11/07/2009 union all
select 4, '250.09', '0', '04/14/2010' union all
select 5, '333.33', '0', '07/10/2010' union all
select 6, '400.25', '0', '02/10/2010' union all
select 7, '423.28', '0', '12/23/2010'

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 13:02:05

[code]SELECT s.*,s1.EarnedAsOfNow
FROM @Sample s
CROSS APPLY(SELECT SUM(earned) AS EarnedAsOfNow
FROM @Sample
WHERE Inv_date < = s.Inv_date
)s1
[/code]

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

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-23 : 13:51:41

Thank you visakh , it worked......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 01:38:32

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources