Formula Calculation embedded in table?

• Naveen J V

  SSC-Addicted

  Points: 446

  More actions

  November 10, 2017 at 3:50 am

  #326163

  Hi Experts,

  I have the following tables T1 and T2 linked by ID. ID 99, 100 & 101 has a formula in T2 table. I need create a SP/UDF, when I input 5000 to Premium column, FieldValue of 99,100 & 101 should be re-calculated and updated based on the formula in T1, that is Tax=4400, Amount=10400 and Percentage=104. There might be dependency of formula with each other, but it should be calculated on FormulaOrder.

  T1
  

  T2

  

  Sample Data:

  create table #T1
  (
    ID int, FieldName varchar(50),fieldvalue varchar(50),fieldtypeid int,fieldorder int
  );

  insert into #T1(ID,FieldName,fieldvalue,fieldtypeid,fieldorder)
  values (97,'Coverage','This is Coverage',486,1),
  (98,'Premium','6000',485,2),
  (99,'Tax','10',488,3),
  (100,'Amount','10',488,4),
  (101,'Percentage',null,488,5);

  create table #T2
  (
    ID int, Formula varchar(50),FormulaOrder int
  );

  insert into #T2(ID,Formula,FormulaOrder)
  values (99,'Premium * 0.8',1),
  (100,'Premium + Tax',2),
  (101,'Amount * 0.01',3);

  Please do help.

  Thanks in advance

  Naveen J V

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  November 10, 2017 at 5:35 am

  #1967505

  Naveen J V - Friday, November 10, 2017 3:50 AM

  Please do help.
  

  With which part, exactly? 

  • Table redesign?
  • Solution redesign?
  • Architecture review?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 14, 2017 at 8:30 am

  #1967983

  Deleted

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 14, 2017 at 8:33 am

  #1967987

  Naveen J V - Friday, November 10, 2017 3:50 AM

  Hi Experts,

  I have the following tables T1 and T2 linked by ID. ID 99, 100 & 101 has a formula in T2 table. I need create a SP/UDF, when I input 5000 to Premium column, FieldValue of 99,100 & 101 should be re-calculated and updated based on the formula in T1, that is Tax=4400, Amount=10400 and Percentage=104. There might be dependency of formula with each other, but it should be calculated on FormulaOrder.

  T1
  

  T2

  

  Sample Data:

  create table #T1
  (
    ID int, FieldName varchar(50),fieldvalue varchar(50),fieldtypeid int,fieldorder int
  );

  insert into #T1(ID,FieldName,fieldvalue,fieldtypeid,fieldorder)
  values (97,'Coverage','This is Coverage',486,1),
  (98,'Premium','6000',485,2),
  (99,'Tax','10',488,3),
  (100,'Amount','10',488,4),
  (101,'Percentage',null,488,5);

  create table #T2
  (
    ID int, Formula varchar(50),FormulaOrder int
  );

  insert into #T2(ID,Formula,FormulaOrder)
  values (99,'Premium * 0.8',1),
  (100,'Premium + Tax',2),
  (101,'Amount * 0.01',3);

  Please do help.

  Thanks in advance

  Naveen J V

  That kind of thing is not a good idea within a database.  You'd have to write your own formula parser, and thtat's a lot more work than what a volunteer on a public forum would be likely to attempt.   Given that SQL Server can accomplish something similar with "calculated fields", I'd suggest going in that direction instead, as this one is impractical at best.   Look up "SQL Server calculated fields" on Google.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)