I have been asked to perform some excel like optimization in SQL. I need to loop through some variables(which will take discrete values, jumping by 0.05) and find for each variable the value that minimizes a distance that is a function of that variable. I'm not very used to work with dynamic variables so any kind of help would be highly appreciated.
--Loop trough each variable to be optimized
declare @j as integer
set @j = 1
while @j <= 50
begin
declare @i as integer
set @i=0
--Here is where I don't know how to do it, I need the variable to be declared in each loop
--If the next deviation is deviating from the prior, means that the prior iteration reached the best fit. Equations in this case are linear
do while @deviation'+'@i <deviation'+'(@i-1)
declare @deviation'+'@i as float
--Update the table each time with the recalculated target variable
Select a.*, a.product_price * b.product_fee as changing_value into calculator
from product_tables a left join fee_table b
on a.Product_code = b.Product_code
Recalculate function distance to be minimized
set @deviation'+'@i = sum(SQRT(Current_value - changing_value)^2)) from calculator
update fee_table
set product_fee = product_fee +@i*0.05 where id_fee = @j
set @i = i+1
end
--- If the condition is met, means that the (@i-1) value is the best fit
update fee_table
set product_fee = product_fee +(@i-1)*0.05 where id_fee = @j
set @j= j+1
end
I apologize in advance for the inconsistencies you may find in the code. Any kind of help will be highly appreciated!!