October 7, 2008 at 8:56 am
Hi,
I am storing formula(Math Expressions) details in the database.
I will explain you with an example.I have two table in sqlserver database.
1)ItemTable:Which consists of raw data.
ItemID itemvalue Date
1 22 2/9/2008
2 26 2/9/2008
3 55 2/9/2008
12 98 2/9/2008
13 87 2/9/2008
1 25 2/10/2008
2 76 2/10/2008
3 34 2/10/2008
12 46 2/10/2008
13 43 2/10/2008
Where ITemid+date forms the composite key.
Requirement is to calcuate some formulas(Math Expressions) based on this raw data.
The formula details(Math Expressions) are mentioned below FormulaTable.
2)FormulaTable:Which consits of formula details.
FormulaID Formula Date
1 ((2+12)/13)*120 2/9/2008
2 ((12+13)/13)*150 2 /9/2008
3 ((2+3)/12*)150 2/9/2008
1 ((2+12)/13)*100 2/10/2008
2 ((12+13)/13)*100 2/10/2008
3 ((2+3)/12*)100 2/10/2008
Lets calucate the formula(Math Expressions) on the 2/9/2008
1 st formula=(2+12)/13(where 2,12 and 13 are the itemid's in the ItemTable).
The result of the formula should be ((26+98)/55)*120=270(where 26,98 and 55 are the corresponding values for that itemid's in the ITemTable).
Result of the formula on the 2/10/2008 is ((26+98)/55)*100=2.25
How do we replace itemid with corresponding itemvalue specified in the formula and calculate the formula dynamically.
Thanks In Advance
Regards,
Sriram Satish
October 8, 2008 at 2:24 am
if your formula is always the same, I suggest it is not the proper way of table design.
You will have to put the three attributes into three different fields in formula table. This will be the better maintainable. It will make easier to relate with Item table using joins.
October 10, 2008 at 6:57 am
This is quite easy to do... I hope you're here to learn and not just get homework assignments 😉
In a business world, we often do not have the choice to restructure data. So we must find solutions. In any case, here you go:
Your example doesn't quite match your table. Here's what I get for formula 1 for 2/9/2008:
select ((26.0+98.0)/87.0)*120.0 --result is 171.034482758620689600
--formula 1 (result is 171.034482758620689600)
select ((v1 + v2)/v3) * 120.0
from (
select max(f1) as v1, max(f2) as v2, max(f3) as v3
from (
select case when ItemID = 2 then ItemValue * 1.00 end as f1,
case when ItemID = 12 then ItemValue * 1.00 end as f2,
case when ItemID = 13 then ItemValue * 1.00 end as f3
from #itemtable
where Date = '2/9/2008'
) as t1
) as t2
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply