Using an If or Then statement in MSSQL to calulate a field dependant on the value of another field in the same table

Posted on 2004-11-12
Medium Priority
Last Modified: 2012-05-05
I am new at SQL and am trying to design a calculated field that is dependent on the value of another field


I have a "total_Comm field" who's calculated value is dependent on the value of a "VAT field" either being yes or no and "Comission_paid field" so that if the value is yes then the calculated field will be "Comission_paid field" * 1.175 or if no then "Comission_paid field" * 1

I have tried the alter table statement below but keep getting a syntax error. Also I am not sure that the logic of this is right or if there is a better way to do this

ALTER TABLE [Actual Recuring Comm]
  ADD Total_Comm AS
    CASE Commission_paid
       WHEN 0 THEN 0
IF "VAT = Yes"
THEN (Comission_paid * 1.175)
IF "VAT = No"
THEN Commission_paid * 1

Any help or pointers would be appreciated

Question by:21agdgcf

Accepted Solution

SashP earned 500 total points
ID: 12563471

ALTER TABLE [Actual Recuring Comm]
  ADD Total_Comm AS
        when Commission_paid = 0 then 0
        when [Vat Column Name] = 'Yes' then [Comission_paid column name] * 1.175
        when [Vat Column Name] = 'No' then [Comission_paid column name]

Author Comment

ID: 12563524
Many thanks for quick respose it works fine

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question