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
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
    LVL 8

    Accepted Solution


    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

    Many thanks for quick respose it works fine

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how the fundamental information of how to create a table.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now