HireDate with Different Dates in Employee

[mabuhay87]

Count the Employee having different hire date and show the lastest hire date to a new column.

My table below:

EmployeeNo HireDate TerminationDate

11 1/1/2009 6/5/2009
12 5/5/2009
13 6/2/2009
14 6/15/2009 11/1/2009
15 7/1/2009
11 9/8/2009
14 2/5/2010

The output should be like this:



EmployeeNo-------- HireDate --------- TerminationDate-------------- RehireDate-(New Column)

11 ------------------- 1/1/2009 ---------- 6/5/2009
12 ------------------- 5/5/2009
13 ------------------- 6/2/2009
14 ------------------- 6/15/2009 ---------11/1/2009
15 ------------------- 7/1/2009
11------------------- 9/8/2009------------------------------------------------9/8/2009
14 ------------------ 2/5/2010-------------------------------------------------2/5/2010

Please help. Thanks.

[amrut]

Does following query helps ?

SELECT Table1.EmpID, Min(Table1.HireDate) AS MinOfHireDate as FirstHireDate, Max(Table1.HireDate) AS ReHireDate
FROM Table1
GROUP BY Table1.EmpID;

change the field/table names for your case

[rpeare]

probably don't want a double alias in that example amrut

SELECT Table1.EmpID, Min(Table1.HireDate) AS FirstHireDate, Max(Table1.HireDate) AS ReHireDate FROM Table1 GROUP BY Table1.EmpID;

[mabuhay87]

Rehire Date field should not be showing the first hire date, it should be blank. Only showing the Rehire Date record.

Please help. Thanks.

[rpeare]

can I ask what you're trying to do with this query because what you're asking for in your original example doesn't really make sense to me. If you're trying to create a report or query with hire/termination history there are better ways to do this, if you're trying to indicate whether or not someone was a rehire on their most recent record there's an easier way to do that too.

[mabuhay87]

Can you please give an examples the best way to do it? I want the history of hire/termination.
Thank you for your help.

[rpeare]

SELECT EmployeeNo, HireDate, TerminationDate FROM TABLENAME ORDER BY EmployeeNo, HireDate

This will sort your date FIRST by employee, SECOND by Hire Date, so your most recent hire will show LAST, if you want to show their most recent hire FIRST you would do

SELECT EmployeeNo, HireDate, TerminationDate FROM TABLENAME ORDER BY EmployeeNo, HireDate DESC