October 16, 2015 at 2:53 am
I have 2 tables, one of those table is master formula which has some records like this :
Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
4Rate street lighting
5AVG Installed Capacity
6Consumed a: Minimum Charges
7Consumed a: Minimum Charges (*)
8Consumed b: other than Minimum Charges
9Consumed b: other than Minimum Charges[3]*[8]
10Total Consumed a+b[6]+[8]
11Total Consumed a+b[7]+[9]
12Street lighting[4]*[11]/100
13Admin fee([11]+{12])/9
14TOTAL 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
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
Thanks..
October 16, 2015 at 7:38 am
gentong.bocor (10/16/2015)
I have 2 tables, one of those table is master formula which has some records like this :Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
4Rate street lighting
5AVG Installed Capacity
6Consumed a: Minimum Charges
7Consumed a: Minimum Charges (*)
8Consumed b: other than Minimum Charges
9Consumed b: other than Minimum Charges[3]*[8]
10Total Consumed a+b[6]+[8]
11Total Consumed a+b[7]+[9]
12Street lighting[4]*[11]/100
13Admin fee([11]+{12])/9
14TOTAL 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
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
Thanks..
What an awful design. You are going to have to write dynamic sql with a separate query for each value you have to lookup. This is going to be a bit challenging to say the least. If you can turn these two tables into something consumable I can help you get started. By consumable I mean create table statements and insert statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2015 at 8:57 am
As Sean said, this is an awful design. These should be columns instead of rows. All of these columns are totals and should be treated like that. The formulas can be created as computed columns if they won't change or implemented in a SP or trigger if subject to change.
October 17, 2015 at 8:42 pm
Yes, that's 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..
October 21, 2015 at 7:24 am
gentong.bocor (10/17/2015)
Yes, that's 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..
No offense but this data structure is a total train wreck. SQL Server is not a programming language, it is a data storage and retrieval system. Sure it can do some things but what you are doing here would be awful to code in a programming language. In t-sql it is just plain and simple the complete wrong approach. You have recursion where you should be storing values. You have formulas embedded in the data instead of storing values. You are going to have to look at each every row in this table over and over for these calculations. There is just nothing right about this in any way shape or form. Sure this could be done but it would be awful to write it. You have to split your strings to get external row references and do lots of token replacement only to calculate this stuff in dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2015 at 7:42 am
Sean Lange (10/21/2015)
gentong.bocor (10/17/2015)
Yes, that's 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..
No offense but this data structure is a total train wreck. SQL Server is not a programming language, it is a data storage and retrieval system. Sure it can do some things but what you are doing here would be awful to code in a programming language. In t-sql it is just plain and simple the complete wrong approach. You have recursion where you should be storing values. You have formulas embedded in the data instead of storing values. You are going to have to look at each every row in this table over and over for these calculations. There is just nothing right about this in any way shape or form. Sure this could be done but it would be awful to write it. You have to split your strings to get external row references and do lots of token replacement only to calculate this stuff in dynamic sql.
+1000
Don't start this project in this way. Sure you could make it work, eventually, but it would be a nightmare to do, it would be even more difficult to get any decent performance from it, and maintenance would be very difficult. Have you considered constructing a library of table-valued functions as an alternative?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply