Optimization in SQL with Nested Dynamic Variables

39 views Asked by At

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!!

0

There are 0 answers