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: 246695

  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?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• sgmunson

  SSC Guru

  Points: 110639

  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: 110639

  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)