how to transfer this iterative solution to a set-based solution

Question

/*
Stored Procedure
April 30, 2021
Mohamad Chaker
*/

USE CIS111_BookStoreMC
GO
--drop the procedure
IF OBJECT_ID('spAssetInfo') IS NOT NULL
    DROP PROC spAssetInfo 
GO
--Create the stored procedure
CREATE PROC spAssetInfo

AS
--Create a temporary table to display the inventory
SELECT AssetID, Description, Cost, PurchaseDate 
INTO #temptable
FROM Assets

--Add a new column to the temporary table displaying the date an asset is completely depreciated
ALTER TABLE #temptable ADD CurrentValue MONEY

DECLARE @years INT;
DECLARE @currentYear INT;
SET @currentYear = YEAR(getdate());
DECLARE @cost MONEY;

--Add a new column to the temporary table to display the current value of the item in the current year
ALTER TABLE #temptable ADD CompleteDepreciationYear DATE

--@value holds the cost of an asset and is used to check when the value of an asset drops low
DECLARE @value MONEY;
SET @value = 0.00;
--@counter is an int that I used to iterate over the table rows
DECLARE @counter INT;
--@depreciationNum holds the amount of years until an item is completely depreciated to be later used in DATEADD() 
DECLARE @depreciationNum INT;
SET @counter = 1;

DECLARE @assetsTableSize INT;
SET @assetsTableSize = (SELECT COUNT(*) FROM Assets);

WHILE (@counter <= @assetsTableSize)
    BEGIN
        --Current Value
        SET @years = @currentYear - (select YEAR(PurchaseDate) From Assets Where AssetID = @counter);
        SET @cost = (select Cost From Assets Where AssetID = @counter);
        --calculate current value of each asset
        WHILE(@years>0)
            BEGIN
                SET @cost = @cost * 0.8;
                SET @years = @years - 1;
            END
        --add the current value of each asset to the temporary table
        UPDATE #temptable 
        SET CurrentValue = @cost
        WHERE AssetID = @counter;
        
        --Deprection Year
        SET @depreciationNum = 0;
        SET @value = (select Cost From Assets Where AssetID = @counter);
        WHILE(@value >0.1)
            BEGIN  
                SET @value = @value * 0.8;
                SET @depreciationNum = @depreciationNum + 1;
            END
        --add the date each asset is completely depreciated to the temporary table
        UPDATE #temptable 
        SET CompleteDepreciationYear = CAST(DATEADD(year, @depreciationNum, (select PurchaseDate From Assets Where AssetID = @counter)) AS DATE)
        WHERE AssetID = @counter;
        --increment the counter
        SET @counter = @counter + 1;
    END
--display the assets inventory
SELECT * FROM #temptable

Prompt: Show an asset inventory along with the current value (minus 20% depreciation per year). Also show the year when each item will be completely depreciated.

basically I am trying to show an Asset Inventory with PurchaseDate and the date the item is completely depreciated, the asset depreciates 20% per year. I tried to do a temporary table and copy some of the assets table columns to it then adding a column for the date when the asset completely depreciates.

I implemented this using an iterative solution but I was advised to post on SO to try and do this using a set-based implementation. I am new to SQL and newly learned that it's a set-based language and that it isn't very good with iterative solutions.

Thank you in advance!

Answer 1

The formula to calculate the current value is:

CurrentValue = InitialValue * (1-0.2)^(Today - InitialDate)

To find the NumberOfYears that the asset will reach 0.1:

0.1 = CurrentValue * (1-0.2)^NumberOfYears. Taking the log from both sides:

Log(0.1) = Log(CurrentValue * Log(0.8^NumberOfYears)

Log(0.1) = Log(CurrentValue) + Log(0.8^NumberOfYears)

Log(0.1) = Log(CurrentValue) + NumberOfYears * Log(0.8)

[Log(0.1) - Log(CurrentValue)] / Log(0.8) = NumberOfYears

You can create a function that use this formula and returns the NumberOfYears:

CREATE  FUNCTION GetNumberOfYears(
    @InitialValue FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @CurrentValue FLOAT,
        @InitialValue FLOAT,
        @YearsToZero FLOAT;

-- CurrentValue Formula
SET @CurrentValue = @InitialValue * POWER(CAST(0.8 AS FLOAT), YEAR(GETDATE() - YEAR(PurchaseDate));

-- NumberOfYears Formula
SELECT @YearsToZero = (LOG(0.1) - LOG(@CurrentValue)) / LOG(0.8);

RETURN @YearsToZero;
END

It will give you the number of years (e.g. 54.6724159 years).

Then use it like SELECT GetNumberOfYears(Cost) FROM Assets

Or, you can use directly from your SELECT:

SELECT
    Cost * POWER(CAST(0.85 AS FLOAT), YEAR(GETDATE()) - YEAR(PurchaseDate)) AS CurrentValue,
    (LOG(0.1) - LOG( [the above CurrentValue formula] )) / LOG(0.8) AS YearsToZero
 FROM Assets