Asset Depreciation Calculation - Straight Line Method

Basit Khan

Hi,

I have Asset table.

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

GO
and the data is give below.

INSERT INTO [Accounts].[dbo].[Asset]
([item]
,[bought_date]
,[price]
,[AssetLife]
)
VALUES
('x'
,'2020-01-01'
,11000
,8),

('y'
,'2020-01-01'
,8000
,5)

Below is the Formual

Depreciation Expense= Book Value/Asset Life.
Accumulated Dep.=Depreciation expense
Book Valve Year End=Asset Value-Accumulated Dep.

Looking for query for below result.

Asset	Years	Book Value Year Start	Depreciation Expense	Accumulated Depreciation	Book Value Year End
X	2020	11,000	1375	1,375	9,625
X	2021	9,625	1375	2,750	8,250
X	2022	8,250	1375	4,125	6,875
X	2023	6,875	1375	5,500	5,500
X	2024	5,500	1375	6,875	4,125
X	2025	4,125	1375	8,250	2,750
X	2026	2,750	1375	9,625	1,375
X	2027	1,375	1375	11,000	-

Thanks

Basit.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
Accepted answer
Check the below queries
IF OBJECT_ID('tempdb..#finaltable') IS NOT NULL DROP TABLE #finaltable
CREATE TABLE #finaltable (
    [Asset] VARCHAR(50),
    [Years] INT,
    [Book Value Year Start] NUMERIC(18, 2),
    [Depreciation Expense] NUMERIC(18, 2),
    [Accumulated Depreciation] NUMERIC(18, 2),
    [Book Valve Year End] NUMERIC(18, 2)
)

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp (
    sqlid INT IDENTITY, item nvarchar(50),
    boughtdate date, price numeric(18, 2),
    assetlife int
)
INSERT INTO #temp SELECT item, bought_date, price, assetlife FROM [dbo].[Asset]
--SELECT * FROM #temp
DECLARE @count INT = 1, @count1 INT = (SELECT COUNT(*) FROM #temp)
WHILE @count <= @count1
BEGIN
    DECLARE @asset NVARCHAR(50) = (SELECT item FROM #temp WHERE sqlid = @count)
    DECLARE @assetyear INT = (SELECT YEAR(boughtdate) FROM #temp WHERE sqlid = @count)
    DECLARE @assetprice NUMERIC(18, 2) = (SELECT price FROM #temp WHERE sqlid = @count)
    DECLARE @AssetLife INT = (SELECT assetlife FROM #temp WHERE sqlid = @count)
    DECLARE @BookValueYearStart NUMERIC(18, 2) = @assetprice

    DECLARE @lifeCount INT = 1, @lifecount1 INT = @AssetLife
    WHILE @lifeCount <= @lifecount1
    BEGIN
        DECLARE @DepreciationExpense NUMERIC(18, 2) = @BookValueYearStart/@AssetLife
        DECLARE @BookValueYearEnd NUMERIC(18, 2) = @BookValueYearStart - @DepreciationExpense
        DECLARE @AccumulatedDepreciation NUMERIC(18, 2) = @assetprice - @BookValueYearEnd

        INSERT INTO #finaltable VALUES (@asset, @assetyear, @BookValueYearStart, @DepreciationExpense, @AccumulatedDepreciation, @BookValueYearEnd)

        SET @BookValueYearStart = @BookValueYearEnd
        SET @assetyear = @assetyear + 1
        SET @AssetLife = @AssetLife - 1

        SET @lifeCount = @lifeCount + 1
    END
    SET @count = @count + 1
END
SELECT * FROM #finaltable
Basit Khan4y ago
Many Many Thanks.

Once again thank you very much.