Asset Depreciation Calculation - Straight Line Method

Question

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.

Amit Mohanty · 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 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 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 Khan · Answer

Many Many Thanks. Once again thank you very much.

Asset Depreciation Calculation - Straight Line Method

Answers (2)