creating calculation scripts

Question

creating calculation scripts

Nomvula

Hall of Fame

Points: 3778
More actions
August 19, 2008 at 5:08 am

#191322

hi
i have a table which look like this:
IDDepreciation
510040
547960
58585-19079.347
62466-113.57
66291-113.57
70143-113.57
74025-113.57
77933-113.57
81849-113.57
on my script i need to calculate a Accummulative Depreciation on another field and the rule is on the first entry the Depreciation will remain the same, the second entry should take the value from the prevous entry add it to the current entry i.e for ID 51004 and 54796 AccumDepr will be 0 cause there's no Depreciation value this id 58585 should equal to 19079.347 cause there's no previous depr, and on ID 62466 my AccumDepr should be (-19079.347) + (-113.57) = -19192.91 and the next ID should be -19192.91 + -113.57 = -19306.48 and so forth...
please help

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 1

if your ID values are random as shown in the data example, you will have to either iterate using a loop or use a cursor. If they are in an order, there can be other possible ways.

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

my id is unique value... what other possible ways there could be.

i tried this approach:

SELECT Depreciation.Depreciation, Depreciation.TransactionID,Depreciation.Depreciation +

coalesce

(

select sum(Dep.Depreciation) from Depreciation Dep

where Dep.TransactionID < Depreciation.TransactionID

)

, 0) as AccummulativeDepreciation

from Depreciation

but doesn't give me the results i need...

please assist

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 3

I think this should help

select id, Depreciation, Depreciation + isnull((select Depreciation from TABLE where id = (select max(id) from TABLE where id < x.id)),0) from

TABLE x

😀

Michael Meierruth SSChampion Points: 10051 More actions · Answer 4

Oops! Isn't this a triangular join? Jeff will pounce on you for this.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

declare @Table table(

ID int not null,

Dep money,

Accum money

);

insert @Table

(ID, Dep)

select 51004, 0 union all

select 54796, 0 union all

select 58585, -19079.347 union all

select 62466, -113.57 union all

select 66291, -113.57 union all

select 70143, -113.57 union all

select 74025, -113.57 union all

select 77933, -113.57 union all

select 81849, -113.57;

declare @Accum money;

set @Accum = 0;

update @Table

set @Accum = Accum = @Accum + Dep;

select *

from @Table;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

Ahh, that feels better.

But is this truly set based?

Or is this just a SS2000 hack?

I wonder if SS2005 has something to deal with this directly in a select.

john.arnott SSChampion Points: 11882 More actions · Answer 7

Tomm,

Does your solution depend on the temp table being read back in the same order in which it was loaded? That "gotcha" would have to be avoided at least by using an "order by" clause.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 8

john.arnott (8/22/2008)
Does your solution depend on the temp table being read back in the same order in which it was loaded? That "gotcha" would have to be avoided at least by using an "order by" clause.

Yes, you are correct. But the OP never indicated what order was important (or even if order was important) so I had no choice but to also omit it.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

j nuñez SSCrazy Points: 2175 More actions · Answer 9

Could this sentences satisfy your requirements?

declare @Table table(

ID int not null,

Dep money,

Accum money

);

insert @Table

(ID, Dep)

select 51004, 0 union all

select 54796, 0 union all

select 58585, -19079.347 union all

select 62466, -113.57 union all

select 66291, -113.57 union all

select 70143, -113.57 union all

select 74025, -113.57 union all

select 77933, -113.57 union all

select 81849, -113.57;

select T1.ID, T1.Dep +

ISNULL((SELECT SUM(T2.Dep)

FROM @Table T2

WHERE T2.ID < T1.ID

),0)

from @Table as T1

ORDER BY T1.ID

Nomvula Hall of Fame Points: 3778 More actions · Answer 10

hi serinor

the query you sent did exactly what i needed, and now i can continue with my work cause that was holding me up.

thanks again and all of you guys who did contribute with this.