Hi,
I am writing a query that retrieve the employee information... my query is:
SELECT PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
PPF.FULL_NAME EMPLOYEE_NAME,
TO_CHAR(PPF.DATE_OF_BIRTH,’DD/MM/YYYY’) EMPLOYEE_DOB,
PPF.NATIONALITY EMPLOYEE_NATIONALITY_CODE,
PPF.PAYROLL_ID EMPLOYEE_PAYROLL_CODE
FROM PER_PEOPLE_F PPF,
PER_ALL_ASSIGNEMENTS_F PASF
WHERE TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID=PASF.PERSON_ID
AND PASF.PRIMARY_FLAG=’Y’
this code return the information for the active employee in the current day, i also need to return this information for all ex-employees.
now i am thinking about the best way how to get this done...
should I use per_periods_of_service_v with actual_termination_date is not null??
should i also check for the person_type_id in the per_person_type_usages_f??
is it better to join the query above with the one that return the info for the ex-employee, or is it better to return the max(pasf.effective_end_date) in case of the ex-employee and the sysdate in case of the current to be something like:
(
SELECT MAX(PASF1.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F PASF1
WHERE -- CONDITIONS FOR THE EX_EMP
UNION
SELECT TRUNC(SYSDATE)
FROM PER_ALL_ASSIGNMENTS_F PASF1
WHERE -- CONDITIONS FOR THE ACTIVE_EMP
) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
So, how should i write my query
appreciate your help