Getting Current and Previous EFFDTed Rows

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.

Read More About  EFFDTed Rows