user1282609 user1282609 - 4 months ago 11
SQL Question

Calculate column based on other column values in SQL Server

The result of

sql
select statement looks as below:

select * from table

Location Role Days
-----------------------------------
Hong Kong Senior Associate 48
Canberra Senior Associate 60
Sydney Senior Associate 55


I want to add a column to above result -
Hours
(there is no hours column in the SQL Server table).

Hours
should be calculcated based on location


  • If Hong Kong then Days/7

  • If Canberra then Days/7.5

  • If Sydney then Days/7.25



The result should be like:

Location Role Days Hours
-----------------------------------------
Hong Kong Senior Associate 48 6.85
Canberra Senior Associate 60 8
Sydney Senior Associate 55 7.586


I tried with
select case
which will check only one condition (if and else) and column should be in database.

select
case
when location = 'Sydney'
then Days/7
else Days/7.5
end as Hours


But I want all to be conditions to be checked in one SQL statement and add hours column which is not in database.

Any advice please.

Answer

A case solves this:

select location, role, days,
       (case when location = 'Hong Kong' then days / 7.0
             when location = 'Canberra' then days / 7.5
             when location = 'Sydney' then days / 7.25
        end) as hours
from t;

You can even add this column to the table using a computed column:

alter table t
    add hours as (case when location = 'Hong Kong' then days / 7.0
                       when location = 'Canberra' then days / 7.5
                       when location = 'Sydney' then days / 7.25
                  end);
Comments