Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: SQL Newbie - Conditional Calculation - Best Practice?

    I hope someone can help with this.

    I want to create a view with 5 columns:
    [condition], [value1], [value2], [value3], [calculation]

    The calculation is based on the value of the condition e.g.

    Condition = A: Calculation is [Value1]+[value2]+[value3]
    Condition = B: Calculation is [Value1]*[value2]*[value3]
    Condition = C: Calculation is [Value1]/[value2]/[value3]

    I thought I could add an IIF statement to the calculation field or i could group all the same conditions in one view, perform the calculation then create a new view joining all the views for the different calculations.

    Has anyone had experience with this who could steer me in the right direction.

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just use a CASE expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    5
    Are you referring to this?

    CASE

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by russellhq View Post
    Are you referring to this?
    yup
    Code:
    CASE WHEN condition = 'A'
         THEN [Value1]+[value2]+[value3]
         WHEN condition = 'B'
         THEN [Value1]*[value2]*[value3]
         WHEN condition = 'C'
         THEN [Value1]/[value2]/[value3]
      END  AS calculation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    5
    Cheers, that's a big help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •