CODEWITHSUNDEEP
sqlsql-server-2008
prospectofdeath
prospectofdeath

Reputation: 13

SQL conditional SELECT with possible NULL

I've tried searching for this on stackoverflow but I can't find the answer.

I'm simplifying my problem so it reads easier, I'll expand if necessary.

I've got a Employee SQL table with NAME, ADDRESS, HIRE_DATE and we recently added a REHIRE_DATE to keep track of when new employees restart with the company. I'd like to write a SQL search to pull the NAME, ADDRESS and their most recent hire date. If they're a rehire I'd use the REHIRE_DATE field but if they're original use the HIRE_DATE field. I'm guessing it's a CASE statement that's looking for a NULL in the REHIRE_DATE field but beyond that I'm lost.

Upvotes: 1

Views: 6306

Answers (4)

Rashlien
Rashlien

Reputation: 294

You could also use the coalesce() function and do it this way

select name, address, coalesce(rehire_date,hire_date) from table_name

if the first value is null it will go to the second value

Upvotes: 0

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

I think all you need to do is this:

SELECT
    NAME
    , ADDRESS
    , ISNULL(REHIRE_DATE, HIRE_DATE) AS MostRecentHireDate
FROM Employee

In this case the rehire date should always be greater than the hire date if it is filled out (You can't get re-hired before you get hired. So you just always want to take the rehire date unless it is null, at which point you take the hire date. Sound good?

Upvotes: 5

Nicholas Shanks
Nicholas Shanks

Reputation: 10971

Better still, use IFNULL():

SELECT IFNULL(REHIRE_DATE,HIRE_DATE) FROM Employee

Upvotes: 0

Taryn
Taryn

Reputation: 247690

This should produce the results you want:

SELECT Name
    , Address
    , CASE WHEN REHIRE_DATE IS NULL THEN HIRE_DATE ELSE REHIRE_DATE END
FROM Employee

This checks if the Rehire Date, if it is NULL then you will get the Hire Date if it isn't then you will return the Rehire Date.

Upvotes: 4

Related Questions