Need Help for performing below calculation

Question

I have a certain value say 10 as a base for my calculation. Now the growth for the first period corresponding to value 10 is 5 . The resultant which i want is 10*(1+5/100) which is basically Base * (1+ % of Growth) . The resultant Value for the first period will be the new Base for the next period. Assuming the next growth be 6 , the result for the next period is (10(1+5/100)) * (1+ 6/100) . This is basically running multiplication and can be achieved using many ways. Now someone please suggest the ultimate best way to achieve this calculation.

1. 10 , 5 --> 10 (1 + 5/100) = 10.50
2. 10.50 , 6 --> 10.50 (1 + 6/100) = 11.1300
3. 11.13 , any value and so on

The method I've tried using other data sample but which is running multiplication basically.

CREATE TABLE #t1
  (
     projection_details_sid INT,
     period_sid             INT,
     growth                 NUMERIC(22, 6)
  )

INSERT INTO #t1
            (projection_details_sid,
             period_sid,
             growth)
VALUES      ( 1,601,2 ),
            ( 1,602,2 ),
            ( 1,603,2 ),
            ( 1,604,1 ),
            ( 1,605,6 ),
            ( 1,606,3 )

SELECT *,
       Exp(Sum(Log(growth))
             OVER (
               PARTITION BY projection_details_sid
               ORDER BY projection_details_sid ROWS UNBOUNDED PRECEDING ))
FROM   #t1 

Answer 1

Try a recursive query.
The below example is for Oracle, but it can be easily adopted to SQL-Server.

WITH our_recursive_query(projection_details_sid,  period_sid, growth,  base,  our_result)
AS (
    select projection_details_sid,  period_sid, growth, 
           10.0 as base, 
           10 * ( 1 + growth/100) As our_result
    from t1 where period_sid = 601

    UNION ALL

    SELECT a.projection_details_sid,  a.period_sid, a.growth, 
           b.our_result as base, 
           b.our_result * ( 1 + a.growth/100) As our_result
    FROM t1 a
    JOIN our_recursive_query b
    ON a.period_sid = b.period_sid + 1
)
SELECT * FROM our_recursive_query

and a result is:

                 PROJECTION_DETAILS_SID PERIOD_SID     GROWTH         BASE   OUR_RESULT
--------------------------------------- ---------- ---------- ------------ ------------
                                      1        601          2  10.00000000  10.20000000
                                      1        602          2  10.20000000  10.40400000
                                      1        603          2  10.40400000  10.61208000
                                      1        604          1  10.61208000  10.71820080
                                      1        605          6  10.71820080  11.36129285
                                      1        606          3  11.36129285  11.70213163

I am assumming that period_sid is increassing by 1, therefore I am using .period_sid = b.period_sid + 1 as a join condition. If this is not true in your real data, you need to modify slighly the query with use of row_number analytic function.

---

EDIT

@kordiko thanks. is there any other possible to achieve the result other than recursive cte since recursive cte has a similar performance of while loop.

Yes. In Oracle you can create your own aggregate function that performs a multiply of a chain of numbers - in the similar way as a built-in sum function does ==> X1+X2+...+Xn, but does X1*X2*....*Xn instead.

An example:

create or replace TYPE MyCumulativeMultiply_type
AS OBJECT (

cumulativeMultiplyResult NUMBER,

STATIC FUNCTION odciaggregateinitialize(ctx IN OUT MyCumulativeMultiply_type) RETURN NUMBER,

MEMBER FUNCTION odciaggregateiterate(self IN OUT MyCumulativeMultiply_type, your_parameter_to_aggregate IN NUMBER) RETURN NUMBER,

MEMBER FUNCTION odciaggregatemerge(self IN OUT MyCumulativeMultiply_type, ctx2 IN MyCumulativeMultiply_type) RETURN NUMBER,

MEMBER FUNCTION odciaggregateterminate(self IN MyCumulativeMultiply_type, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY MyCumulativeMultiply_type
IS
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT MyCumulativeMultiply_type)
        RETURN NUMBER
    IS
    BEGIN
          -- instantiate our type, NULL the dummy attribute
        ctx  := MyCumulativeMultiply_type( 1 );
        RETURN odciconst.success;
    END odciaggregateinitialize;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT MyCumulativeMultiply_type, your_parameter_to_aggregate IN NUMBER)
        RETURN NUMBER
    IS
    BEGIN
        self.cumulativeMultiplyResult := self.cumulativeMultiplyResult * your_parameter_to_aggregate; 
        RETURN odciconst.success;
    END odciaggregateiterate;

    MEMBER FUNCTION odciaggregatemerge(self IN OUT MyCumulativeMultiply_type, ctx2 IN MyCumulativeMultiply_type)
        RETURN NUMBER
    IS
    BEGIN
       self.cumulativeMultiplyResult := self.cumulativeMultiplyResult * ctx2.cumulativeMultiplyResult;
        RETURN odciconst.success;
    END odciaggregatemerge;

    MEMBER FUNCTION odciaggregateterminate(self          IN   MyCumulativeMultiply_type,
                                           returnvalue      OUT NUMBER,
                                           flags         IN     NUMBER
                                          )
        RETURN NUMBER
    IS
    BEGIN
        returnvalue := self.cumulativeMultiplyResult;
        RETURN odciconst.success;
    END odciaggregateterminate;
END;
/

CREATE OR REPLACE FUNCTION cumulative_multiply(arg NUMBER)
    RETURN NUMBER
    PARALLEL_ENABLE
    AGGREGATE USING MyCumulativeMultiply_type;
    /

and now a query is:

select t1.*
      , cumulative_multiply(  1 + growth/100 ) OVER (order by period_sid ) as multiplier
      , 10 * cumulative_multiply(  1 + growth/100 ) OVER (order by period_sid ) as our_result
from t1;

and a result is:

                 PROJECTION_DETAILS_SID PERIOD_SID     GROWTH   MULTIPLIER   OUR_RESULT
--------------------------------------- ---------- ---------- ------------ ------------
                                      1        601          2   1.02000000  10.20000000
                                      1        602          2   1.04040000  10.40400000
                                      1        603          2   1.06120800  10.61208000
                                      1        604          1   1.07182008  10.71820080
                                      1        605          6   1.13612928  11.36129285
                                      1        606          3   1.17021316  11.70213163

Unfortunately I don't know it the above approach is possible in SQL-Server.

Answer 2

You can get idea from below query:-

DECLARE @Growth INT = 5
    ,@Base DECIMAL(18,2) = 10.0;

;WITH Test(Base, Growth)
AS
(
    SELECT @Base, @Growth

    UNION ALL

    SELECT CAST(t.Base * (1 + t.Growth/100.0) AS DECIMAL(18,2))  , t.Growth + 1
    FROM Test t
    WHERE t.Base < 600000000
)

SELECT * 
FROM Test
option (maxrecursion 0)