Recursive multiplication sql server

Question

Is there a way to multiply one number by a ratio, the outcome of this multiplication by another ratio, and again the outcome of this last one by another ratio?

The multiplication would be:

100+100*0.1=110

110+110*0.3=143

143+143*0.2=171.6

I have the following tables 'revenue' and 'ratios' and I would like to get a query result as 'outcome'. I guess I should use a recursive query but I am not sure how to write it I am using SQL server

with
revenue as (
select '100' revenue 
)
,
ratios as
(
select '1' month_n, '0,1' ratio
union
select '2' month_n, '0,3' ratio
union
select '3' month_n, '0,2' ratio
),
outcome as
(
select '1' month_n, '0,1' ratio, '110' result
union
select '2' month_n, '0,3' ratio, '143' result
union
select '3' month_n, '0,2' ratio, '171,6' result
)

select * from outcome

What version of SQL Server are you using? – GarethD Jul 01 '15 at 14:22 — GarethD, Jul 01 '15 at 14:22

Me.Name · Accepted Answer · 2015-07-01T14:28:55.633

If the months are increasing without gaps, recursion can be done by joining on the month_n - 1:

with
revenue as (
select 100 revenue 
)
,
ratios as
(
    select 1 month_n, 0.1 ratio
    union
    select 2 month_n, 0.3 ratio
    union
    select 3 month_n, 0.2 ratio
),
outcome as
(
    select (select MIN(month_n) from ratios) - 1 MONTH_n, 0.0 ratio, cast(revenue as float) result, 1 IsBase from revenue   
    union all
    select r.month_n , r.ratio, (r.ratio + 1.0) * o.result, 0
    from ratios r
    join outcome o on o.MONTH_n = r.month_n - 1
)
select MONTH_n,ratio,result  from outcome where IsBase = 0

Result:

MONTH_n ratio   result
1   0.1 110
2   0.3 143
3   0.2 171,6

But safer would be using a row_number for the index, especially if multiple years come into the picture.

As a totally unimportant side note, you could also write this without the superfluous 'base' row as done underneath, but that would put the calculation definition in both lines. Not that that causes extra calculations, just a maintainability fling.

....,
outcome as
(
    select top 1 MONTH_n,  ratio, (ratio + 1.0) *  cast(revenue as float) result from revenue , ratios order by month_n
    union all
    select r.month_n , r.ratio, (r.ratio + 1.0) * o.result
    from ratios r
    join outcome o on o.MONTH_n = r.month_n - 1
)
select *  from outcome

Many thanks Me.Name. That was the answer I was looking for when I was trying to solve it with a recursion. But you also mentioned trying a lead/lag function, which might make it even simpler since the real query is much more complex than the example I gave — vdBurg, Jul 01 '15 at 15:56

score 1 · Answer 2 · edited May 23 '17 at 11:58

If your ratios are non null, and always greater than -1 (since you need to add 1 anyway to get the multiplication factor) and you are also using SQL Server 2012 or later then your query can be solved without recursion:

WITH Revenue (Revenue) AS (SELECT 100),
Ratios AS (SELECT * FROM (VALUES (1, 0.1), (2, 0.3), (3, 0.2)) t (Month_n, Ratio))

SELECT  ra.month_n,
        ra.Ratio,
        CompoundRatio = EXP(SUM(LOG((1 + ra.Ratio))) OVER(ORDER BY Month_n)),
        Result = rv.Revenue * EXP(SUM(LOG((1 + ra.Ratio))) OVER(ORDER BY Month_n))
FROM    Ratios AS ra
        CROSS JOIN Revenue AS rv;

Which gives:

month_n Ratio   Compound    Result
-------------------------------------
1       0.1     1.1         110
2       0.3     1.43        143
3       0.2     1.716       171.6

You can use windowed functions to sum up the previous ratios, to get a compound ratio, although since SQL Server does not have a product aggregate you need to use LOG and EXP to get the product of the previous ratios.

If your ratios can be negative and/or 0 then you can still do this, but your logic gets a bit more complicated. See this answer for more of an explanation of what the checks are doing:

WITH Revenue (Revenue) AS (SELECT 100),
Ratios AS (SELECT * FROM (VALUES (1, 0.1), (2, 0.3), (3, 0.2), (4, -0.1), (5, 0.2), (6, -0.2), (7, 0.05), (8, -1.1)) t (Month_n, Ratio))

SELECT  ra.month_n,
        ra.Ratio,
        CompoundRatio = CASE WHEN MIN(ABS(ra.Ratio + 1)) OVER(ORDER BY ra.Month_n) = 0 THEN 0
                            ELSE CASE WHEN SUM(CASE WHEN ra.Ratio < -1 THEN 1 ELSE 0 END) 
                                            OVER(ORDER BY ra.Month_n) % 2 = 1 THEN -1 ELSE 1 END *
                                EXP(SUM(LOG(ABS(NULLIF(1 + ra.Ratio, 0)))) OVER(ORDER BY Month_n))
                        END,
        Result = CASE WHEN MIN(ABS(ra.Ratio + 1)) OVER(ORDER BY ra.Month_n) = 0 THEN 0
                    ELSE CASE WHEN SUM(CASE WHEN ra.Ratio < -1 THEN 1 ELSE 0 END) 
                                    OVER(ORDER BY ra.Month_n) % 2 = 1 THEN -1 ELSE 1 END *
                        EXP(SUM(LOG(ABS(NULLIF(1 + ra.Ratio, 0)))) OVER(ORDER BY Month_n))
                END * rv.Revenue
FROM    Ratios AS ra
        CROSS JOIN Revenue AS rv;

Results (With sanity check):

month_n Ratio   CompoundRatio   SQLResult       Equation
------------------------------------------------------------------------
1       0.1     1.1             110             100 + 100 x 0.1 = 110
2       0.3     1.43            143             110 + 110 x 0.3 = 143
3       0.2     1.716           171.6           143 + 143 x 0.2 = 171.6
4       -0.1    1.5444          154.44          171.6 + 171.6 x -0.1 = 154.44
5       0.2     1.85328         185.328         154.44 + 154.44 x 0.2 = 185.328
6       -0.2    1.482624        148.2624        185.328 + 185.328 x -0.2 = 148.2624
7       0.05    1.5567552       155.67552       148.2624 + 148.2624 x 0.05 = 155.67552
8       -1.1    -0.15567552     -15.567552      155.67552 + 155.67552 x -1.1 = -15.567552

Thanks GarethD. I am using SQL server 2012, but I have indeed many negative ratios. I guess that by using EXP it won´t work with negative ratios — vdBurg, Jul 01 '15 at 14:55
Are your negative values less than -1? Each ratio is having 1 added to it anyway - rewriting your first equation of `100 + 100 * 0.1` is equivalent to `100 * (1 + 0.1)`, so even if your ratio was -0.9 this would not be a problem for the current method. If you do have values lower than this then I will add an updated formula to account for this. — GarethD, Jul 01 '15 at 15:22
No, no, they can´t be less than -1. So ok, i will try your first solution too — vdBurg, Jul 01 '15 at 15:50
Thanks GarethD, it took me some time to adapt it to the real query but it works perfectly — vdBurg, Jul 03 '15 at 13:08

Recursive multiplication sql server

2 Answers2