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