Formula Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Formula Problem

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-18 : 06:57:58

--DROP table #F
CREATE TABLE #F (Fid int,FormulaName Nvarchar(25),Formula Nvarchar(6))
insert into #F
SELECT 1,'Sum','A' UNION ALL
SELECT 1,'Sum','+' UNION ALL
SELECT 1,'Sum','B' UNION ALL
SELECT 2,'First','Sum' UNION ALL
SELECT 2,'First','*' UNION ALL
SELECT 2,'First','C' UNION ALL
SELECT 3,'Secon','First' UNION ALL
SELECT 3,'Secon','/' UNION ALL
SELECT 3,'Secon','Sum' UNION ALL
SELECT 4,'Third','Secon' UNION ALL
SELECT 4,'Third','+' UNION ALL
SELECT 4,'Third','E'
Select * from #F

--DRop table #A
Create Table #A(CompanyID int,[Year] int,Tickar Nvarchar(5),[Value] int)
insert into #A
SELECT 1,2010,'A',15 UNION ALL
SELECT 1,2010,'B',25 UNION ALL
SELECT 1,2010,'C',35 UNION ALL
SELECT 1,2010,'D',12 UNION ALL
SELECT 1,2010,'E',13 UNION ALL
SELECT 1,2010,'F',50 UNION ALL
SELECT 1,2010,'G',40 UNION ALL
SELECT 2,2010,'H',35
SELECT * FROM #A

Declare @Formula Nvarchar(50),@Result varchar(25),@Col Nvarchar(Max),@Query nvarchar(Max)
Select @Formula=COALESCE(@Formula+Formula,Formula) FROM #F Where FID=1--Here I am passing FormulaID 1 From TABLE #F

SELECT @Col=COALESCE(@Col+',['+ Tickar +']','['+Tickar+']') From #A
--Select @Col
Set @query=N'SELECT '+ @Formula + ' FROM (Select [Value],Tickar From #A )p PIVOT (MAX([Value]) For Tickar IN ('+@Col+')) AS PVT'
Exec (@query)

/* Now I need to convert this to hierachy level See My Second Formula in #F having Sum
Which is A+B and * C
I need output
1400
I have solved this also but every time when user add one more level using fron end then it is dificlat to calculate.
User can Dynamically change his formula using front end.
For Example User add one more formula using Front end like FormulaID 4 which having 3 level
Where
Third=Secon+E ----------------(1)
In above (1) Secon=First/Sum------------------(2)
Again in (2) First=Sum*C And Sum=A+B-----------------(3)
Again in (3) Sum=A+B

So Fina calculation of FormulaID 4 in table #F is
Third=(((A+B)*C)/(A+B))+E

This level can be dynamically increased I cant able to fix this problem in which the formula can be dynamically increased
Using SP(Stored Procedure) I can fix some level like 4 to 12 level every time it will take loop and it calculate but it
take long time so any simple ideal

And Please Don't Tell That this I am making complicated its actually my client Requirement; The client plan
is, in future they don't need service from us*/

Raghu' S

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 07:09:50

Add a hierarchy level column to the table and update it to reflect the dependencies. Then you can loop through using this value - calculating in order and also perhaps replacing in order.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-18 : 07:39:08

But how to find levels?

Raghu' S

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 07:52:04

You have sum referenced in First, Secon referenced in Third.
Level 1 = no references.
Level 2 = references from level 1 only
Level 3 = references from level 1 and 2 only ...

Create a table to save the results as you loop so from level 1 you get
Sum, iii
From level 2
Sum, iii
First iii
From level 3
Sum, iii
First iii
Secon

I would also have something to distinguish references in formulae
<SUM> or %SUM% or <%SUM%>

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-18 : 08:10:14

Oh! Sorry, This is all on-fly Calculation so I am not saving calculated values or result in other table.
And this calculation done on current year. I don't have problem with years. I only need to find how many levels are there in formula;
I used #F and #A tables with dummy data
It may be something like this also
Sub=A-B
This Formula name Sum...Sub..First..bla.. bla.....in table #F are created using Front end and only selected user can edit or create.

Raghu' S

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 08:14:24

Then looking at references will enable you to calculate the level.
Loop through starting with formulae with no references until you get to the formula you want to calculate. I would add this column to the formula table but you can calculate it on the fly if you wish.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-18 : 08:24:38

Yep! But see my FormulaID 3 it divide in two formula so here I again need to run two time loop until there are no references.So this is problem I can't able to figure it out!

Raghu' S

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 08:54:31

If you used the identifiers I suggested earlier

while not exists (select * from #tbl where Formula like '%</%%' escape '/')
begin
-- replace references in formulae with values already calculated
calculate formula with no references
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources