Find the oldest joinee department wise using LEAD Analytic function
LEAD is exact opposite of the LAG Analytic function. LAG fetches the row details of previous record and LEAD fetches the row details of next record.
SELECT Employee_Id,
First_Name,
Department_Id,
Hire_date,
LEAD(Hire_date) OVER(PARTITION BY Department_Id ORDER BY Hire_date DESC) as PREV_HIREDATE
FROM EMPLOYEES
ORDER BY Department_Id, Hire_date;
Since LEAD fetches the next value we used the DESC in the ORDER BY clause so that the LEAD Analytic function starts working from latest Hire date and keep picking the hire date of the employee joined before him. So finally for the oldest joinee the value will be NULL.
The below query fetches the oldest joinee details department wise:
SELECT * FROM(
SELECT Employee_Id,
First_Name,
Department_Id,
Hire_date,
LEAD(Hire_date) OVER(PARTITION BY Department_Id ORDER BY Hire_date DESC) as PREV_HIREDATE
FROM EMPLOYEES)
WHERE PREV_HIREDATE IS NULL;
Also Read :
What is a Synchronization task in Informatica?
What is a Runtime environment in Informatica?
What is Check In Check Out in Informatica powercenter (Versioning)
Filter transformation in Informatica?