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.