Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Sep 2013
    Location
    Accra,Ghana
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Asset Monthly Depreciation

    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;

    drop trigger if exists insert_purchases;

    drop procedure if exists process_purchase;

    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.E stimated_Useful_Life,new.Monthly_Depreciation,new. Estimated_Residual_Value,new.Accumulated_Depreciat ion,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.E stimated_Useful_Life,new.Monthly_Depreciation,new. Estimated_Residual_Value,new.Accumulated_Depreciat ion,new.Asset_ID);
    END|

    delimiter ;

    -- insert some sample data

    insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(2500,5);
    insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(2000,4);

    insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(3000,6);

    insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(1500,3);

    insert into asset_update (Cost_Of_Acquisition,Estimated_Useful_Life) values(5000,9);

    -- update asset_update set Total = 0;
    can this be achieved using java? if yes pls help and if anyone can also modify mysql code i will appreciate that too
    Please serious help will be appreciated since am working with time THANK YOU

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,945
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    I have to tell you I would not do it that way, at all.

    I would instead simply store the cost, useful_life, and when_acquired.

    And then use a query that would calculate the accumulated depreciation on any given date by comparing the given date with when_acquired.

    Your system is EXTREMELY vulnerable to error. If somebody runs the code to (example) add September's depreciation to all records on September 1st but then another person *ALSO* does that on September 3rd, then you would have accumulated two months of depreciation when you should have only accumulated one.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,945
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Here, this is what I am talking about:
    Code:
    CREATE TABLE assets (
      name varchar(50)
      cost decimal(20,2)
      life int
      whenacquired date
    );
    
    // some sample data:
    mysql> select * from assets;
    +----------+---------+------+--------------+
    | name     | cost    | life | whenacquired |
    +----------+---------+------+--------------+
    | aardvark | 2500.00 |    5 | 2012-01-01   |
    | bobcat   | 2000.00 |    4 | 2012-03-01   |
    | cougar   | 3000.00 |    6 | 2013-01-01   |
    | deer     | 5000.00 |    4 | 2010-01-01   |
    +----------+---------+------+--------------+
    
    // my query:
    SELECT name, cost AS originalCost, whenacquired, monthsDepreciated, monthlyDepreciation, 
           monthsDepreciated * monthlyDepreciation AS totalDepreciation,
           cost - monthsDepreciated * monthlyDepreciation AS depreciatedValue
    FROM (
        SELECT name, 
               cost,
               whenacquired, 
               (YEAR(NOW())*12 + Month(NOW())) - ( YEAR(whenacquired)*12 + Month(whenacquired)) AS monthsDepreciated,
               cost / ( life * 12 ) AS monthlyDepreciation
        FROM assets
        ) AS X
    ORDER BY whenacquired;
    
    // gives these results:
    +----------+--------------+--------------+-------------------+---------------------+-------------------+------------------+
    | name     | originalCost | whenacquired | monthsDepreciated | monthlyDepreciation | totalDepreciation | depreciatedValue |
    +----------+--------------+--------------+-------------------+---------------------+-------------------+------------------+
    | deer     |      5000.00 | 2010-01-01   |                44 |          104.166667 |       4583.333348 |       416.666652 |
    | aardvark |      2500.00 | 2012-01-01   |                20 |           41.666667 |        833.333340 |      1666.666660 |
    | bobcat   |      2000.00 | 2012-03-01   |                18 |           41.666667 |        750.000006 |      1249.999994 |
    | cougar   |      3000.00 | 2013-01-01   |                 8 |           41.666667 |        333.333336 |      2666.666664 |
    +----------+--------------+--------------+-------------------+---------------------+-------------------+------------------+
    Hopefully you can see that you can replace the NOW() (used two places) in that query with any date you want to get the depreciation information as of that date.

    And it only takes a bit more work to make sure an asset is not depreciated below zero.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,945
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Oh, what the heck. Here I show a fully-depreciated asset and how to handle it in the query:
    Code:
    +----------+---------+------+--------------+
    | name     | cost    | life | whenacquired |
    +----------+---------+------+--------------+
    | aardvark | 2500.00 |    5 | 2012-01-01   |
    | bobcat   | 2000.00 |    4 | 2012-03-01   |
    | cougar   | 3000.00 |    6 | 2013-01-01   |
    | deer     | 5000.00 |    4 | 2010-01-01   |
    | eagle    | 2000.00 |    3 | 2009-01-01   |
    +----------+---------+------+--------------+
    
    SELECT name, cost AS originalCost, whenacquired, life,
           IF (monthsDepreciated >= life * 12, life*12, monthsDepreciated) AS monthsDepreciated, 
           monthlyDepreciation, 
           IF (monthsDepreciated >= life * 12, cost, monthsDepreciated * monthlyDepreciation) AS totalDepreciation,
           IF (monthsDepreciated >= life * 12, 0, cost - monthsDepreciated * monthlyDepreciation) AS depreciatedValue
    FROM (
        SELECT name, 
               cost,
               life,
               whenacquired, 
               (YEAR(NOW())*12 + Month(NOW())) - ( YEAR(whenacquired)*12 + Month(whenacquired)) AS monthsDepreciated,
               cost / ( life * 12 ) AS monthlyDepreciation
        FROM assets
        ) AS X
    ORDER BY whenacquired;
    
    +----------+--------------+--------------+------+-------------------+---------------------+-------------------+------------------+
    | name     | originalCost | whenacquired | life | monthsDepreciated | monthlyDepreciation | totalDepreciation | depreciatedValue |
    +----------+--------------+--------------+------+-------------------+---------------------+-------------------+------------------+
    | eagle    |      2000.00 | 2009-01-01   |    3 |                36 |           55.555556 |           2000.00 |                0 |
    | deer     |      5000.00 | 2010-01-01   |    4 |                44 |          104.166667 |       4583.333348 |       416.666652 |
    | aardvark |      2500.00 | 2012-01-01   |    5 |                20 |           41.666667 |        833.333340 |      1666.666660 |
    | bobcat   |      2000.00 | 2012-03-01   |    4 |                18 |           41.666667 |        750.000006 |      1249.999994 |
    | cougar   |      3000.00 | 2013-01-01   |    6 |                 8 |           41.666667 |        333.333336 |      2666.666664 |
    +----------+--------------+--------------+------+-------------------+---------------------+-------------------+------------------+
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Sep 2013
    Location
    Accra,Ghana
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for that correction but how will i be able to get the accumulated value of that monthly depreciation every month? Thanks Again

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,945
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    As i said, just replace NOW() with the date you want the numbers for. Or you could easily display a depreciation schedule for any one or several items.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •