I have a data set like this:

i want calculate percentage increase of a number based on this data.

for example: i have a input number => 100

calculated number for 100 is :

100 5.0% = 105

105 7.0% = 112.35

112.35 2.3% = 114.93405

Can i do this in T-Sql ?

CREATE function [dbo].[fn_sample] (@input decimal(29,19)) returns decimal(29,19)  as

begin

DECLARE @tmp TABLE([Year] int, [Percentage] decimal(29,19))
INSERT INTO @tmp ([Year], [Percentage]) VALUES
(1990, 5),
(1991, 7),
(1992, 2.3)

--TODO Calculate number
return @input
end

CodePudding user response：

This can be done with entirely set-based SQL. No loops or recursion needed:

CREATE function [dbo].[fn_sample] (@input decimal(29,19)) returns decimal(29,19)  as
begin

    DECLARE @tmp TABLE([Year] int, [Percentage] decimal(29,19));

    INSERT INTO @tmp ([Year], [Percentage]) VALUES
    (1990, 5),
    (1991, 7),
    (1992, 2.3)
    ;

    DECLARE @output decimal(29,19) = 1.00;

    SELECT @output = EXP(SUM(LOG((100.00 Percentage)/100))) 
    FROM @tmp

    return @output;
end