Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.
SELECT
A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT
FROM PS_JOB A, PS_JOB B
WHERE
B.EFFDT =(SELECT MAX(A_ED1.EFFDT) FROM PS_JOB A_ED1
WHERE B.EMPLID = A_ED1.EMPLID
AND B.EMPL_RCD = A_ED1.EMPL_RCD
AND A_ED1.EFFDT < A.EFFDT)
AND B.EFFSEQ =(SELECT MAX(A_ES1.EFFSEQ) FROM PS_JOB A_ES1
WHERE B.EMPLID = A_ES1.EMPLID
AND B.EMPL_RCD = A_ES1.EMPL_RCD
AND B.EFFDT = A_ES1.EFFDT)
AND A.EMPLID = B.EMPLID
AND A.EMPLID='KA0002′
Is this the only way to get the results?????
Here is the Simple and Advanced way to achieve the same results without using Self Join
SELECT
A.EMPLID, A.EFFDT CURR_EFFDT, LAG (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) PREV_EFFDT
FROM PS_JOB A
WHERE A.EMPLID='KA0002′
Like this, we can also get the Current and Following EFFDTed rows by using LEAD Function
SELECT
A.EMPLID, A.EFFDT CURR_EFFDT, LEAD (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) LEFFDT
FROM PS_JOB A
WHERE A.EMPLID='KA0002′
Note: Above SQL holds good for Oracle DB. In Oracle, The Function LAG/LEAD is called Analytic Function, which is there right from Oracle 8i onwards.
No comments:
Post a Comment