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
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.
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