 # How to make query to calculate a dynamic formula?

#1

I have 2 tables, one of those table is master formula which has some records like this :

Table1: MasterFormula

``````No	Description				Formula
1	Occupancy
2	Leased
3	Rate Consumed
4	Rate street lighting
5	AVG Installed Capacity
6	Consumed a: Minimum Charges
7	Consumed a: Minimum Charges (*)
8	Consumed b: other than Minimum Charges
9	Consumed b: other than Minimum Charges	*
10	Total Consumed  a+b			+
11	Total Consumed  a+b			+
12	Street lighting				*/100
13	Admin fee				(+{12])/9
14	TOTAL SALES - B2			++``````

Table2: TransactionFormula
Only has 2 fields, No & Amount

the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is + means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9

Here's the example result for TransactionFormula records

``````No	Amount
1	100
2	100
3	10
4	3
5	10
6	10
7	20
8	10
9	20	+
10	20	+
11	40	+
12	1.2	*/100
13	4.58	(+{12])/9
14	45.78	++``````

actually my design is calculating the formula based on the value of each rows. I made it because the user can freely adding any rows for a new formula. You know, excel can put any formula which calculate for each rows.
Here's i give you more details on what it looks like

``````CREATE TABLE [FormulaTemplate](
[BudgetID] [varchar](10) NOT NULL,
[SeqNo] [int] NOT NULL,
[Description] [varchar](100) NOT NULL,
[Description1] [varchar](100) NOT NULL,
[Formula] [varchar](50) NOT NULL
)

insert into FormulaTemplate values ('B.01',1,'Occupancy','%','')
insert into FormulaTemplate values ('B.01',2,'Leased','SQM','')
insert into FormulaTemplate values ('B.01',3,'Rate Consumed','IDR','')
insert into FormulaTemplate values ('B.01',4,'Rate street lighting','%','')
insert into FormulaTemplate values ('B.01',5,'AVG Installed Capacity','KVA','')
insert into FormulaTemplate values ('B.01',6,'Consumed a: Minimum Charges','KWH','')
insert into FormulaTemplate values ('B.01',7,'Consumed a: Minimum Charges (*)','IDR','')
insert into FormulaTemplate values ('B.01',8,'Consumed b: other than Minimum Charges','KWH','')
insert into FormulaTemplate values ('B.01',9,'Consumed b: other than Minimum Charges','IDR','*')
insert into FormulaTemplate values ('B.01',10,'Total Consumed  a+b','KWH','*')
insert into FormulaTemplate values ('B.01',11,'Total Consumed  a+b','IDR','*')
insert into FormulaTemplate values ('B.01',12,'Street lighting','IDR','*/100')
insert into FormulaTemplate values ('B.01',13,'Admin fee','IDR','(+{12])/9')
insert into FormulaTemplate values ('B.01',14,'TOTAL SALES - B2','IDR','++')

CREATE TABLE [BudgetTransaction](
[BudgetID] [varchar](10) NOT NULL,
[SeqNo] [int] NOT NULL,
[Amount] [money] NOT NULL
)
insert into BudgetTransaction values ('B.01',1,100)
insert into BudgetTransaction values ('B.01',2,100)
insert into BudgetTransaction values ('B.01',3,10)
insert into BudgetTransaction values ('B.01',4,3)
insert into BudgetTransaction values ('B.01',5,10)
insert into BudgetTransaction values ('B.01',6,10)
insert into BudgetTransaction values ('B.01',7,20)
insert into BudgetTransaction values ('B.01',8,10)``````

FormulaTemplate table is the master table for the formula of each BudgetID which maybe in the next time, i can add a new Seqno like SeqNo=15 which keep a new formula. The formula field is an expression formula for calculating amount field from BudgetTransaction table which has previous SeqNo.
The concept is like in excel, where the formula is based on rows.

Inside the UI programming for budget transaction, if we add a new seqno which that SeqNo inside the formula, then it automatically insert a new record in BudgetTransaction for the SeqNo which has formula..
For example :
insert into BudgetTransaction values ('B.01',1,100) -> just insert Seqno=1
insert into BudgetTransaction values ('B.01',2,100) -> just insert SeqNo=2
insert into BudgetTransaction values ('B.01',3,10) -> insert SeqNo=3 then insert SeqNo=9, because SeqNo=3 was calculated in formula for SeqNo=9 which has formula +. But since SeqNo=8 still not yet exists in BudgetTransaction table, so the amount for SeqNo=9 same as SeqNo=3 (10)
insert into BudgetTransaction values ('B.01',4,3) -> insert SeqNo=4, then insert SeqNo=12, because SeqNo=4 was calculated in formula for SeqNo=12 which has formula */100. But since SeqNo=11 still not yet exists in BudgetTransaction table, so the amount for SeqNo=12 become 0

and so on...

Thanks..

#2

Well, I found this intriguing enough to take a stab at it, kinda already knowing how it would turn out. TL;DR: this probably won't work for you the way it's currently designed.

A few problems you'll encounter:

1. SQL is not a general purpose programming language. It doesn't have
an inline EVAL() function for formulas.
2. SQL is relational, and works on values and named attributes, not
positions and relative references. Excel will automatically
resequence formulas if new rows are inserted between. There is no
concept or relevance of an item's position in the relational model,
so inserting a new row will break formulas the way you have them
now.
3. You could work around this (somewhat) by using the Description as
the formula reference instead of SeqNo:
``````[Consumed b: other than Minimum Charges 9] = [Rate Consumed] * [Consumed b: other than Minimum Charges]
``````

Note that I had to rename the formula elements since you duplicated them in the example (another issue that will complicate this).

Making this work dynamically, if it's possible at all, will require lots of dynamic SQL that will be very hard to maintain and troubleshoot. And it's even more complicated if you go with numbered (relative) rather than named (absolute) elements.

I might poke at this a little more but I'm not hopeful there's an easy way to make this work in SQL. If you absolutely have to do it this way, offload the formulas to another programming language, either Excel or some C/C++/C#.

#3

The solution below, is ugly but it works. Experts in this forum most likely can produce faster/slicker solution, but my goal here, was just to see if it was possible. This will produce a temp table #budgettransaction_work which will contain the calculated formulars. (at least you now have somewhere to start):

``````declare @sqlinsert varchar(max);
declare @unsolved int=0;
declare @unsolved_last int=999999999;

select *
into #budgettransaction_work
from budgettransaction
;

select @unsolved=count(*)
from formulatemplate as a
where not exists(select 1
from #budgettransaction_work as b
where b.budgetid=a.budgetid
and b.seqno=a.seqno
)
;

while (@unsolved>0 and @unsolved<@unsolved_last)
begin
with cte1
as (select a.budgetid
,a.seqno
,left(a.formula,charindex('[',a.formula)-1)
+cast(b.amount as varchar(max))
+right(a.formula,len(a.formula)-charindex(']',a.formula,(charindex('[',a.formula))))
as formula_new
from formulatemplate as a
inner join #budgettransaction_work as b
on b.budgetid=a.budgetid
and b.seqno=substring(a.formula
,charindex('[',a.formula)+1
,charindex(']',a.formula,(charindex('[',a.formula)))-charindex('[',a.formula)-1
)
where isnull(a.formula,'') like '%[[]%]%'
and not exists (select 1
from #budgettransaction_work as c
where c.budgetid=a.budgetid
and c.seqno=a.seqno
)
union all
select a.budgetid
,a.seqno
,left(a.formula_new,charindex('[',a.formula_new)-1)
+cast(b.amount as varchar(max))
+right(a.formula_new,len(a.formula_new)-charindex(']',a.formula_new,(charindex('[',a.formula_new))))
as formula_new
from cte1 as a
inner join #budgettransaction_work as b
on b.budgetid=a.budgetid
and b.seqno=substring(a.formula_new
,charindex('[',a.formula_new)+1
,charindex(']',a.formula_new,(charindex('[',a.formula_new)))-charindex('[',a.formula_new)-1
)
where isnull(a.formula_new,'') like '%[[]%]%'
)
,cte2
as (select budgetid
,seqno
,formula_new
from cte1
)
select @sqlinsert=
'insert into #budgettransaction_work'
+' (budgetid,seqno,amount)'
+' values '
+stuff((select ',('''+budgetid+''''
+','+cast(seqno as varchar(max))
+','+formula_new
+')'
from cte1
where isnull(formula_new,'') not like '%[[]%]%'
for xml path('')
)
,1
,1
,''
)
;

--    print @sqlinsert;
exec(@sqlinsert);
set @unsolved_last=@unsolved;
select @unsolved=count(*)
from formulatemplate as a
where not exists(select 1
from #budgettransaction_work as b
where b.budgetid=a.budgetid
and b.seqno=a.seqno
)
;
end;

/*****
at this place, all formulars that could be resolved, are available in #budgettransaction_work table
*****/

if (@unsolved>0)
select 'Unsolvable' as fail
,a.*
from formulatemplate as a
where not exists(select 1
from #budgettransaction_work as b
where b.budgetid=a.budgetid
and b.seqno=a.seqno
)
;
else
select * from #budgettransaction_work;

drop table #budgettransaction_work;
``````

#4

Oh before I forget: formula in seqno 13 is messed up.
Also fields in formula 9, 10 and 11 are multiplied in your sample data (insert statements), but in your sample result they are added.