How to make query to calculate a dynamic formula?

Transact-SQL
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	[3]*[8]
10	Total Consumed  a+b			[6]+[8]
11	Total Consumed  a+b			[7]+[9]
12	Street lighting				[4]*[11]/100
13	Admin fee				([11]+{12])/9
14	TOTAL SALES - B2			[11]+[12]+[13]

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 [3]+[8] 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	[3]+[8]
10	20	[6]+[8]
11	40	[7]+[9]
12	1.2	[4]*[11]/100
13	4.58	([11]+{12])/9
14	45.78	[11]+[12]+[13]

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','[3]*[8]')
insert into FormulaTemplate values ('B.01',10,'Total Consumed  a+b','KWH','[6]*[8]')
insert into FormulaTemplate values ('B.01',11,'Total Consumed  a+b','IDR','[7]*[9]')
insert into FormulaTemplate values ('B.01',12,'Street lighting','IDR','[4]*[11]/100')
insert into FormulaTemplate values ('B.01',13,'Admin fee','IDR','([11]+{12])/9')
insert into FormulaTemplate values ('B.01',14,'TOTAL SALES - B2','IDR','[11]+[12]+[13]')

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 [3]+[8]. 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 [4]*[11]/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.