August 19, 2008 at 5:08 am
hi
i have a table which look like this:
ID Depreciation
51004 0
54796 0
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
August 20, 2008 at 2:00 am
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.
August 20, 2008 at 2:08 am
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
August 20, 2008 at 3:22 am
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
😀
August 21, 2008 at 6:01 am
Oops! Isn't this a triangular join? Jeff will pounce on you for this.
August 21, 2008 at 5:48 pm
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
August 21, 2008 at 8:39 pm
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.
August 22, 2008 at 7:00 pm
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.
August 25, 2008 at 11:03 am
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
August 27, 2008 at 6:02 am
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
August 27, 2008 at 8:29 am
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply