I have two values. How can I multiply the first value by the second, then multiply the result of this multiplication by the first number again, then multiply the result by the first number again, and so on? The number of such repetitions for example 7

I have two columns A and B.

• A: 1.65
• B: 10

And I need result like this in result column:

Result:

16,5, 27,2, 44,9, 74,1, 122,2, 201,7, 332,9

So the multiplication would be:

1.65 * 10 = 16,5
1.65 * 16,5 = 27,2
1.65 * 27,2 = 44,9
etc...

I guess I should use a recursive query but I am not sure how to write it I am using SQL Server.

CodePudding user response：

Recursive solution

with CTE as (
  select 
   1.65 as val1,
   10 as val2
)
, RCTE as (
  select val1, val2, 1 as lvl
  , cast(val1*val2 as decimal(38,16)) as result 
  , cast(power(val1, 1)*val2 as float) as result2
  from CTE
  union all
  select val1, val2, lvl 1
  , cast(val1*result as decimal(38,16))
  , cast(power(val1, lvl 1)*val2 as float) 
  from RCTE
  where lvl < 7
)
select *
from RCTE
GO

val1 | val2 | lvl |               result | result2
---: | ---: | --: | -------------------: | ------:
1.65 |   10 |   1 |  16.5000000000000000 |    16.5
1.65 |   10 |   2 |  27.2250000000000000 |    27.2
1.65 |   10 |   3 |  44.9212500000000000 |    44.9
1.65 |   10 |   4 |  74.1200625000000000 |    74.1
1.65 |   10 |   5 | 122.2981031250000000 |   122.3
1.65 |   10 |   6 | 201.7918701562500000 |   201.8
1.65 |   10 |   7 | 332.9565857578125000 |     333

db<>fiddle here

CodePudding user response：

1.65(1.65(1.65*10)) = (1.65*1.65*1.65)*10 = 1.65^3 * 10

so for 7 repetitions

select power(1.65,7) * 10

CodePudding user response：

If you want to generate many rows for each successive value you can try:

with
n as (
  select 1 as i, cast(10.0 as float) as v
 union all
  select i   1, v * 1.65 from n where i < 10
)
select v from n order by i

Result:

 v                
 ---------------- 
 10               
 16.5             
 27.225           
 44.92125         
 74.1200625       
 122.298103125    
 201.79187015625  
 332.956585757812 
 549.37836650039  
 906.474304725644 

See running example at db<>fiddle.