4
Answers

Asset Depreciation - Straight Method Percentage wise

Hi,

I have table

CREATE TABLE [dbo].[Asset](
    [item] [nvarchar](50) NULL,
    [bought_date] [date] NULL,
    [price] [numeric](18, 2) NULL,
    [AssetLife] [numeric](18, 0) NULL,
    [end_date] [date] NULL,
    [AssetCurrentDate] [date] NULL,
    [AssetDedPercent] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

and the data is 

INSERT INTO [Accounts].[dbo].[Asset]
           ([item]
           ,[bought_date]
           ,[price]
           ,[AssetLife]
           ,[end_date]
           ,[AssetCurrentDate]
           ,[AssetDedPercent])
     VALUES
           ('x',
           '2019-01-01'
           ,'4000000.00'
           ,'10'
           ,null
           ,null
           ,20)
GO

Looking for below result.

Year BookValue(A) Rate(B) Dep.Exp C=A*B YTD. Dep D=A-C
2019         4,000,000 20%               800,000               3,200,000
2020         3,200,000 20%               640,000               2,560,000
2021         2,560,000 20%               512,000               2,048,000
2022         2,048,000 20%               409,600               1,638,400
2023         1,638,400 20%               327,680               1,310,720
2024         1,310,720 20%               262,144               1,048,576
2025         1,048,576 20%               209,715                  838,861
2026             838,861 20%               167,772                  671,089
2027             671,089 20%               134,218                  536,871
2028             536,871 20%               107,374                  429,497

I tried below query.

CREATE FUNCTION [dbo].[fnTally]
       (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;

GO

;with cte as (
Select item, AssetLife, Year(bought_date) as Years,
Cast(price as Numeric(10,2)) as Price, cast(price/AssetLife as Numeric(10,2)) as DepreciationExpense,
Cast(price - price*AssetDedPercent as numeric(10,2)) as BookValueYearEnd,Cast(AssetDedPercent as Numeric(10,2)) as AssetDedPercent
from Asset)

select c.item, c.Years + N - 1 as Years
,cast(c.price - (c.price*c.AssetDedPercent/100 * (N - 1)) as Numeric(10,2)) as [BookValue(A)]
,Cast(c.AssetDedPercent as Numeric(10,2)) as AssetDedPercent
,cast((c.price*c.AssetDedPercent/100 * (N)) as Numeric(10,2)) as [Dep.Exp C=A*B]
,cast(c.price - (c.price*c.AssetDedPercent/100 * N) as Numeric(10,2)) as [Dep.Exp C=A*B]

from cte c
cross apply [dbo].[fnTally] (1, AssetLife) N

Thanks & Regards,

Basit

Answers (4)