Conditional calculation on different columns

Question

I have this table where I need to calculate different columns in SQL Server

So far, I have been able to calculate line_item_total_gst column using this formula line_item_total_including_gst / 11 using the syntax below where the rows are null:

UPDATE dbo.invoice_info
    SET line_item_total_gst = line_item_total_including_gst / 11
WHERE line_item_total_gst IS NULL;

My question is how do I do a calculation based on a condition, for example if gst_applicable column is equal to 'Yes' then perform the calculation above. Else if gst_applicable is equal to 'No' then line_item_total_gst is 0.

Answer 1

You could use a case expression:

UPDATE dbo.invoice_info
SET    line_item_total_gst = 
       CASE gst_applicable  WHEN 'Yes' THEN line_item_total_including_gst / 11
                            ELSE 0
       END
WHERE  line_item_total_gst IS NULL;