Friday 28 December 2007

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

0 comments:

Post a Comment