2

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.

Improve this question
2
  • Maybe you could add a small sample data set and the output you expect? Commented Jul 20, 2012 at 15:21
  • 1
    This is not related to your question, but you may want to consider storing hire dates in a separate table e.g. EmploymentPeriod (EmployeeID (FK), StartDate, EndDate). With your current configuration if an employee restarts for a third time there is nowhere to store the rehire date without losing the last rehire date. If you use a one to many table relationship they can leave and come back as many times as they like without causing database problems. Commented Jul 20, 2012 at 15:36

4 Answers 4

Reset to default
5

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

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.

Improve this answer

4 Comments

That's perfect. I was hung up on using '=NULL' and not 'IS NULL'.
Yeah, you cannot use =NULL you have to use IS NULL :)
Wouldn't it be a bit more clear to use the ISNULL function? ISNULL(REHIRE_DATE, HIRE_DATE) is exactly what you're doing here.
@JeremyPridemore you can use either, I just used CASE for my answer.
0

Better still, use IFNULL():

SELECT IFNULL(REHIRE_DATE,HIRE_DATE) FROM Employee
Improve this answer

2 Comments

IFNULL is not valid SQL-Server syntax. COALESCE or ISNULL would both work though. There is a good answer here explaining the difference between the two.
Sorry, I didn't notice the tag, and the question didn't specify. IFNULL() is what I use every day in MySQL.
0

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

Improve this answer

2 Comments

If both values are not null, you will get both returned.
I know this is old, but for others googling that get to this: if both values are not null you will not get both returned, you will still only get the first non-null result. That's how COALESCE works in SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.