Asset depreciation calculation - straight method percentage wise-CodePudding

I'm looking for Asset Depreciation Calculation - straight method percentage wise.

This is my table structure:

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]

Data is below.

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)

Looking for this result:

Result/Output

For that I tried below query.

;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

fnTally function got it from fnTally function link

This is the function code:

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

Kindly help on this.

CodePudding user response：

You can try this method:

;WITH cte AS
(
    SELECT
        item, AssetLife, 
        YEAR(bought_date) AS Years,
        CAST(price AS Numeric(10, 2)) AS Price, 
        CAST(AssetDedPercent AS Numeric(10, 2)) AS AssetDedPercent
    FROM
        Asset
)
SELECT
    c.item, c.Years   N - 1 AS Years,
    ROUND(c.price * POWER((1 - c.AssetDedPercent / 100), N - 1), 2) AS [BookValue(A)],
    CAST(c.AssetDedPercent AS Numeric(10, 2)) AS AssetDedPercent,
    ROUND(c.price * POWER((1 - c.AssetDedPercent / 100), N - 1) * c.AssetDedPercent / 100, 2) AS [Dep.Exp C=A*B],
    ROUND(c.price * POWER((1 - c.AssetDedPercent / 100), N ), 2) AS [Dep.Exp C=A*B]
FROM
    cte c
CROSS APPLY
    [dbo].[fnTally] (1, AssetLife) N

I suggest to use MS SQL function POWER or POW (e.g. in MySQL).

Link on description:

https://docs.microsoft.com/en-us/sql/t-sql/functions/power-transact-sql?view=sql-server-ver16