If statement based on other column

I have a select statement:

select DATEDIFF(day,[Contract Start Date],[Contract End Date]) as contract_time
from table 1

now, how to add into next column if statement:

contract_time >= 390 then display A
contract_time < 390 then display B
contract_time is null display C? (because Contract start date or End date can be null)

thanks for help!

Answers


Use a case expression:

;With cte as 
(
select DATEDIFF(day,[Contract Start Date],[Contract End Date]) as contract_time,
a,
b,
c
from table 1
)

select contract_time
       case when contract_time is null then c
            when contract_time >= 390 then a
            when contract_time <  390 then b
       end as otherColumn
from cte

note that a, b and c must be all of the same data type.


Use IIF function which supports in SQL Server 2012 otherwise you can also use CASE WHEN...THEN ...END

;With cte_table1 as 
(
   SELECT 
       DATEDIFF(day,[Contract Start Date],[Contract End Date]) as contract_time,
       A,
       B,
       C
   FROM [table 1]
)

SELECT contract_time,
       IIF(contract_time is null,C,
           IIF(contract_time >= 390, A,B))
       as otherColumn
FROM cte_table1

Try like this,

SELECT DATEDIFF(day, [Contract Start Date], [Contract End Date]) AS contract_time
    ,CASE 
        WHEN DATEDIFF(day, [Contract Start Date], [Contract End Date]) IS NULL
            THEN c
        WHEN DATEDIFF(day, [Contract Start Date], [Contract End Date]) >= 390
            THEN a
        WHEN DATEDIFF(day, [Contract Start Date], [Contract End Date]) < 390
            THEN b
        END AS otherColumn
FROM TABLE

Need Your Help

How can I post array field data via cURL?

php arrays post curl

I need to submit POST data in the form:

Javascript regex to validate GPS coordinates

javascript regex

I have a form where a user inserts the GPS coordinates of a location to a corresponding photo. Its easy enough to filter out invalid numbers, since I just have to test for a range of (-90, 90), (-...