SQL Server code to duplicate Excel calculation that includes circular reference

Is there a way to duplicate a formula with a circular r开发者_如何学运维eference from a Excel file into SQL Server? My client uses a excel file to calculate a Selling Price. The Selling Price field is (costs/1-Projected Margin)) = 6.5224 (1-.6) = 16.3060. One of the numbers that goes into the costs is commission which is defined as SellingPrice times a commission rate.

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

They get around the circular reference issue in Excel because Excel allows them to check a Enable Iterative Calculation option and stops the iterations after 100 times.

Is this possible using SQL Server 2005?

Thanks

Don

This is a business problem, not an IT one, so it follows that you need a business solution, not an IT one. It doesn't sound like you're working for a particularly astute customer. Essentially, you're feeding the commission back into the costs and recalculating commission 100 times. So the salesman is earning commission based on their commission?!? Seriously? :-)

I would try persuading them to calculate costs and commissions separately. In professional organisations with good accounting practices were I've worked before these costs are often broken down into operating and non-operating or raw materials costs, which should improve their understanding of their business. To report total costs later on, add commission and raw materials costs. No circular loops and good accounting reports.

At banks where I've worked these costs are often called things like Cost (no commissions or fees), Net Cost (Cost + Commission) and then bizzarely Net Net Cost (Cost + Commission + Fees). Depending on the business model, cost breakdowns can get quite interesting.

Here are 2 sensible options you might suggest for them to calculate the selling price.

Option 1: If you're going to calculate margin to exclude commission then

Price before commission = Cost + (Cost * (1 - Projected Margin))

Selling price = Price before commission + (Price before commision * Commission)

Option 2: If your client insists on calculating margin to include commission (which it sounds like they might want to do) then

Cost price = Cost + (Cost * Commission)

Profit per Unit or Contribution per Unit = Cost price * (1-Projected Margin)

Selling Price = Cost Price + Profit per Unit

This is sensible in accounting terms and a doddle to implement with SQL or any other software tool. It also means your customer has a way of analysing their sales to highlight per unit costs and per unit profits when the projected margin is different per product. This invariably happens as the business grows.

Don't blindly accept calculations from spreadsheets. Think them through and don't be afraid to ask your customer what they're trying to achieve. All too often broken business processes make it as far as the IT department before being called into question. Don't be afraid of doing a good job and that sometimes means challenging customer requests when they don't make sense.

Good luck!

No, it is not possible

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

sql expressions can only refer to expressions that already exist. You can not even write

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

The way to look at databases is as transactional engines that take data from one state into another state in one step (with combination of operators that operate not only on scalar values, but also on sets).

Whilst I agree with @Sir Wobin's answer, if you do want to write some recursive code, you may be able to do it 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)