Skip to main content

Asset Monthly Depreciation

No replies
bankTechy
Offline
Joined: 2013-09-17
Points: 0

i want to calculate the depreciation of an asset and i want my accumulated value to automatically add the previous monthly depreciation value with the current one any time the user calculates for the monthly depreciation, the accumulated value should pick the results from monthly depreciation and add it to the one it has and output the results.
example:monthly depreciation is 40 and accumulated value is 0 the result for accumulated value should be 40
when the value for monthly depreciation is 60 the accumulated value should add 40+60=100.

this is my code in MySql(i got stuck at the accumulated part)

drop trigger if exists update_purchases;# MySQL returned an empty result set (i.e. zero rows).

drop trigger if exists insert_purchases;# MySQL returned an empty result set (i.e. zero rows).

drop procedure if exists process_purchase;# MySQL returned an empty result set (i.e. zero rows).

delimiter |

create procedure process_asset_update (
in Cost_Of_Acquisition double,
in Estimated_Useful_Life double,
inout Monthly_Depreciation double,
inout Estimated_Residual_Value double,
inout Accumulated_Depreciation double,

in Asset_ID integer
)

BEGIN
set monthly_Depreciation = (Cost_Of_Acquisition / Estimated_Useful_Life)/12;
set Estimated_Residual_Value = 10 *(Cost_Of_Acquisition);
set Accumulated_Depreciation = monthly_Depreciation ;

END|# MySQL returned an empty result set (i.e. zero rows).

CREATE TRIGGER `update_asset_update`
before update ON `asset_update`
for each row
BEGIN
call process_asset_update(new.Cost_Of_Acquisition,new.Estimated_Useful_Life,new.Monthly_Depreciation,new.Estimated_Residual_Value,new.Accumulated_Depreciation,new.Asset_ID);
END|# MySQL returned an empty result set (i.e. zero rows).

CREATE TRIGGER `insert_asset_update`
before insert ON `asset_update`
for each row
BEGIN
call process_asset_update(new.Cost_Of_Acquisition,new.Estimated_Useful_Life,new.Monthly_Depreciation,new.Estimated_Residual_Value,new.Accumulated_Depreciation,new.Asset_ID);
END|

delimiter ;

-- insert some sample data

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(2500,5);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(2000,4);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(3000,6);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(1500,3);# 1 row affected.

insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(5000,9);# 1 row affected.

-- update asset_update set Total = 0;

Please seriouse help will be appreciated since am working with time THANK YOU