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.

Posted by Kannappan Krishnan
Comments (2)
December 28th, 2007

Comments (2)

Ganesh - April 30th, 2008

Hi Ganesh - Sorry for late reply. The above SQL is an example. You can include the EFFSEQ criteria into the SQL if necessary. The above one is to create awareness about the usage of LEAD and LAG functions in these kind of scenarios.

Ganesh - February 29th, 2008

But your SQL's are not considering the EFFSEQ field in the JOB record. How will inculde it to get prev effdt and max seq num

Comments are closed.