running value - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

running value

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-02-14 : 03:11:24

hi,

i have a following code:


create table test_RV
(
id int identity(1,1)
,date smalldatetime
,value int
,running_value int
,person_ID int
)

insert into test_RV
select '2006-02-06 00:00:00',5,3,124 union all
select '2006-03-12 00:00:00',5,1,124 union all
select '2006-03-23 00:00:00',5,4,124 union all
select '2006-02-11 00:00:00',2,2,125 union all
select '2006-03-12 00:00:00',3,1,127 union all
select '2006-03-21 00:00:00',3,3,127 union all
select '2006-02-18 00:00:00',5,5,210

what i want to achieve is for each value, to subtract the current running_value
until it's equal or lower than zero. for this state i want to print: date, person_ID
and difference on that day.

e.g. for person_ID = 124

id | value | running_value | date
------------------------------------------
1 5 3 2006-02-06
2 5 1 2006-03-13
3 5 4 2006-03-23

id1: value - running_value (5-3) = 2 (equals to zero or lower than zero not reached)
id2: (new)value - running_value (2-1) = 1 (equals to zero or lower than zero not reached)
id3: (new)value - running_value (1-4) = -3 (condition satisfied;
print: date: 2006-03-23;
person_ID:124;
difference: -3)

thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:27:59

do you mean this?


select * from test_RV
select date,person_ID,value-coalesce(DIFF,0)-running_value as [difference]
from test_RV t
outer apply (select SUM(running_value) AS DIFF
             from test_RV
             where person_ID=t.person_ID
             and id < t.id)t1
where (value-coalesce(DIFF,0)-running_value) < 0

output
----------------------------------------
date	             person_ID	difference
2006-03-23 00:00:00	124	-3
2006-03-21 00:00:00	127	-1

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-02-14 : 03:32:19

visakh, thank you for this code byte,
i forgot to tell you that i'm still running on sql 2000.

any ideas there?

thank you

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-02-14 : 03:35:24

and i also forgot that in output where should be also be included:


2006-02-11 00:00:00,125, 0
2006-02-18 00:00:00,210, 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:37:05

[code]
select date,person_ID,value-coalesce((select SUM(running_value)
from test_RV
where person_ID=t.person_ID
and id < id),0)-running_value as [difference]
from test_RV
where (value-coalesce((select SUM(running_value)
from test_RV
where person_ID=t.person_ID
and id < id),0)-running_value) < 0
[/code]

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-02-14 : 04:06:31

great. thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 02:14:47

Also refer point 4
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources