none
Formula Calculation RRS feed

  • Question

  •  

    Hi ,

    I need to calculate a formula (C=A/B*1oo) and update result set in a column.

    Column A comes from Table XXX and

    Column B comes from Table YYY

    After calculating the formula I need to update values in column C in table YYY.

    Tuesday, April 30, 2013 9:32 AM

Answers

  • Hello,

    Put a case statement around the value. See below

    update y
    set C = (Case b When 0 Then 0 Else (a/b) End) *100
    from xxx as x inner join yyy as y
    on x.id = y.id

    • Marked as answer by SR_MCTS Tuesday, April 30, 2013 10:13 AM
    Tuesday, April 30, 2013 10:04 AM

All replies

  • If both tables reside in same database , then write the update statement something like

    update y set C = a/b*100 from xxx as x inner join yyy as y on x.id = y.id

    here you have to convert column A and Column B to respective datatypes in the query , if the column datatype is apart from int
    Tuesday, April 30, 2013 9:37 AM
  • Yes Both the tables in the same database .

    But Now I am getting Devided By Zero Error.

    Msg 8134, Level 16, State 1, Line 1

    Divide by zero error encountered.

    Tuesday, April 30, 2013 9:59 AM
  • Hello,

    Put a case statement around the value. See below

    update y
    set C = (Case b When 0 Then 0 Else (a/b) End) *100
    from xxx as x inner join yyy as y
    on x.id = y.id

    • Marked as answer by SR_MCTS Tuesday, April 30, 2013 10:13 AM
    Tuesday, April 30, 2013 10:04 AM
  • update y set C = CASE WHEN b!=0 THEN a/b*100 ELSE NULL END from xxx as x inner join yyy as y on x.id = y.id

    Try this:

    Your data has 0.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Tuesday, April 30, 2013 10:09 AM