SQL Server code for duplicate Excel calculations that includes circular reference

Question

Is there a way to duplicate a circular link formula from an Excel file in SQL Server? My client uses an excel file to calculate the selling price. Field Selling Price (costs / 1-predicted margin)) = 6.5224 (1-.6) = 16.3060. One of the numbers included in the price is the commission, which is defined as SellingPrice multiplied by the commission rate.

Costs = 6.5224 Projected Margin = 60% Commission = 16.3060 (Sell Price) * .10 (Commission Rate) = 1.6306 (which is part of 6.5224)

They bypass the round reference problem in Excel because Excel allows them to check the Enable Iterative Calculation option and stop iterations after 100 times.

Is this possible with SQL Server 2005?

thank

Don

Answer 1

This is a business problem, not an IT technology, so it follows that you need a business solution, not an IT solution. This is not like working for a particularly insightful client. In essence, you feed the commission back to expenses and recount the commission 100 times. Thus, the seller receives a commission based on their commission?!? Jokes aside?: -)

. , , . , . .

, , , "" ( ), " " ( " + " ), " ( + ). -, .

, .

1: , ,

= + ( * (1 - ))

= + ( * )

2: , (, -, ),

= + ( * )

= * (1- )

= +

SQL . , , , . .

. , . - - , . , , .

!

Answer 2

,

mysql> select 2+a as a;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

sql , .

mysql> select 2 as a, 2+a as b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

- , ( , , ).

Answer 3

So far, I agree with @Sir Wobin's answer, if you want to write some kind of recursive code, you can do this by abusing Recursive common table expressions :

with RecurseCalc as (
    select CAST(1.5 as float) as Value,1 as Iter
    union all
    select 2 * Value,1+Iter from RecurseCalc where Iter < 100
), FinalResult as (
    select top 1 Value from RecurseCalc order by Iter desc
)
select * from FinalResult option (maxrecursion 100)