SQLTeam.com | Weblogs | Forums

Date query


Hi all,

I am trying to get only one dates that is closer to the hiring date and not sure how to achieve it. For example:
The Hiring Date is 01/05/2016 and there are (for example) two Reviewed Dates, 03/08/2016 and 04/12/2016. I want to get one of the date that is closest to 01/05/2016 out of those two reviewed dates. How do I achieve it?

Below is my simple query:

SELECT Hiring_Date, Reviewed_Date
FROM EmployeeTBL

Current result set is:
Hiring_Date Reviewed_Date
01/05/2016 03/08/2016
01/05/2016 04/12/2016

What I need to achieve is:
Hiring_Date Reviewed_Date
01/05/2016 03/08/2016

Because 03/08/2016 is the closer to the Hiring_Date


Thanks all


Use a "group by" and the aggregate function "max"


SELECT Hiring_Date, MIN(Reviewed_Date) AS Reviewed_Date
FROM EmployeeTBL
GROUP BY Hiring_Date


Thanks Bitsmed and Scott,

I forgot to mentioned that some scenarios has one dates lowest than the Hiring Date, so it could be like this...

Hiring_Date Reviewed_Date
01/05/2016 12/05/2015
01/05/2016 04/12/2016

The Hiring Date is not the actual the date when the Employee be hired and started they first day but it is when the employee being borrowed to another department to do other tasks.

So I am not sure if MIN or MAX will get me to what I need.

Thank you


Maybe as below?? Remember, we know nothing about your data, in any way at all, so directly useable sample data with expected results would be most helpful:

SELECT Hiring_Date, MIN(CASE WHEN Reviewed_Date >= Hiring_Date THEN Reviewed_Date END) AS Reviewed_Date
FROM EmployeeTBL
GROUP BY Hiring_Date


I think this will work, Scott. Thanks again. Let me test it and I will let you know. I really appreciated for all your help.