LEAD & LAG analytic function

Example of LEAD and LAG analytic functions used to determine previous and next records

INSERT /*+ append */ 
INTO STG.REHAB_GET_END_DATES
SELECT
   PAYMENT_CLASSIFICATION_NUMBER,
   CLAIM_WCA_ID,
   PAYMENT_ITEM_AMT,
   SERVICE_DATE_ID,
   LAG_SERVICE,
   DAYS_SINCE,
   RNUM
   ,CASE WHEN PAYMENT_CLASSIFICATION_NUMBER IN ('OR0104')                                                                             THEN LEAD(PAYMENT_CLASSIFICATION_NUMBER) OVER (PARTITION BY CLAIM_WCA_ID, SERVICE_PROVIDER_ID ORDER BY CLAIM_WCA_ID, SERVICE_DATE_ID ASC) END LEAD_PAY_CLSS    --payment class  of next service chronolgically
   ,CASE WHEN PAYMENT_CLASSIFICATION_NUMBER IN ('OR0104')                                                                             THEN LEAD(PAYMENT_ITEM_AMT)              OVER (PARTITION BY CLAIM_WCA_ID, SERVICE_PROVIDER_ID ORDER BY CLAIM_WCA_ID, SERVICE_DATE_ID ASC) END LEAD_PAY_AMT     --payment amount of next service chronolgically
   ,CASE WHEN PAYMENT_CLASSIFICATION_NUMBER IN ('OR0104')                                                                             THEN LEAD(DAYS_SINCE)                    OVER (PARTITION BY CLAIM_WCA_ID, SERVICE_PROVIDER_ID ORDER BY CLAIM_WCA_ID, SERVICE_DATE_ID ASC) END LEAD_DAYS_SINCE  --number of days between this service and next service chronolgically
   ,CASE WHEN LEAD(DAYS_SINCE) OVER (PARTITION BY CLAIM_WCA_ID, SERVICE_PROVIDER_ID ORDER BY CLAIM_WCA_ID, SERVICE_DATE_ID ASC) >= 90 THEN SERVICE_DATE_ID ELSE NULL                                                                                                            END CASE_END_DATE_ID --if next service date >= 90 days then this is the end date of this case
   ,SERVICE_PROVIDER_ID
   ,AGENT_INSURER_ID
   ,WORK_STATUS_ID
   ,RTW_OUTCOME_FG
   ,DIFF_EMP_FG
   ,CASE
      WHEN RNUM = 1 THEN SERVICE_DATE_ID
      WHEN DAYS_SINCE >= 90 THEN SERVICE_DATE_ID ELSE NULL --if days between this service and previous service >=90 then this is a new service hence the service date is the start date of a new case
   END CASE_START_DATE_ID
FROM
   (--as inner plus calculates days between this and previous service
      SELECT
         PAYMENT_CLASSIFICATION_NUMBER,
         CLAIM_WCA_ID,
         PAYMENT_ITEM_AMT,
         SERVICE_DATE_ID,
         LAG_SERVICE,
         NVL(TO_DATE(SERVICE_DATE_ID,'YYYYMMDD') - LAG_SERVICE,0) DAYS_SINCE,
         RNUM,
         SERVICE_PROVIDER_ID,
         AGENT_INSURER_ID,
         WORK_STATUS_ID,
         RTW_OUTCOME_FG,
         DIFF_EMP_FG
      FROM
         (-- as inner plus notes the date of previous service
            SELECT
               PAYMENT_CLASSIFICATION_NUMBER,
               CLAIM_WCA_ID,
               PAYMENT_ITEM_AMT,
               SERVICE_DATE_ID,
               RNUM,
               LAG(TO_DATE(SERVICE_DATE_ID,'YYYYMMDD')) OVER (PARTITION BY CLAIM_WCA_ID, SERVICE_PROVIDER_ID ORDER BY CLAIM_WCA_ID, SERVICE_DATE_ID ASC) LAG_SERVICE, --gets the previous service
               SERVICE_PROVIDER_ID,
               AGENT_INSURER_ID,
               WORK_STATUS_ID,
               RTW_OUTCOME_FG,
               DIFF_EMP_FG
            FROM
               (--sums of payments by PCC group and numbered list of txns earliest to latest (this over last 13 quarters)
                  SELECT
                     PAYMENT_CLASSIFICATION_NUMBER,
                     CLAIM_WCA_ID,
                     SUM(PAYMENT_ITEM_AMT) PAYMENT_ITEM_AMT,  --sum amounts on the PCC groups
                     SERVICE_DATE_ID,
                     ROW_NUMBER() OVER (PARTITION BY CLAIM_WCA_ID, SERVICE_PROVIDER_ID ORDER BY CLAIM_WCA_ID, SERVICE_DATE_ID ASC) RNUM, --order and number claims by SP, ascending, earliers is 1
                     SERVICE_PROVIDER_ID,
                     AGENT_INSURER_ID,
                     WORK_STATUS_ID,
                     RTW_OUTCOME_FG,
                     CASE
                        WHEN INSTR((LISTAGG(DIFF_EMP_FG,'') WITHIN GROUP (ORDER BY SERVICE_DATE_ID)),'Y',1,1) > 0 THEN 'Y'
                        ELSE 'N'
                     END DIFF_EMP_FG --not sure what this means or is for...
                  FROM 
                     (--group PCC into 2 groups, looking back for service dates in the last 13 quarters
                        SELECT
                           CASE
                              WHEN DPC.PAYMENT_CLASSIFICATION_NUMBER IN ('OR01', 'OR04') THEN 'OR0104'
                              WHEN DPC.PAYMENT_CLASSIFICATION_NUMBER IN ('OR02', 'OR03') THEN 'OR0203'
                           END PAYMENT_CLASSIFICATION_NUMBER,
                           S.CLAIM_WCA_ID,
                           S.PAYMENT_ITEM_AMT,
                           S.SERVICE_DATE_ID,
                           S.SERVICE_PROVIDER_ID,
                           S.AGENT_INSURER_ID,
                           S.WORK_STATUS_ID,
                           S.RTW_OUTCOME_FG
                           ,CASE
                              WHEN DPC.PAYMENT_CLASSIFICATION_NUMBER = 'OR02' THEN 'N'
                              WHEN DPC.PAYMENT_CLASSIFICATION_NUMBER = 'OR03' THEN 'Y'
                              ELSE 'N'
                           END DIFF_EMP_FG
                        FROM
                           BID.S_CLAIM_PAYMENT_BY_SERVICE S,
                           BID.D_SERVICE_TYPE D,
                           BID.D_PAYMENT_CLASSIFICATION DPC
                        WHERE
                           S.SERVICE_TYPE_ID = D.SERVICE_TYPE_ID
                           AND S.PAYMENT_CLASSIFICATION_ID = DPC.PAYMENT_CLASSIFICATION_ID
                           AND D.SERVICE_TYPE_CODE = 'OR'
                           --AND S.SERVICE_DATE_ID > (SELECT TO_NUMBER(TO_CHAR(ADD_MONTHS(MAX(LAST_COMPLETE_MONTH), -24),'YYYYMMDD')) FROM BID.SNAPSHOT_CONTROL) -- limit data retention to 24 months
                           --AND S.AGENT_INSURER_ID = 5
                           --AND S.CLAIM_WCA_ID IN (8092666)
                           AND DPC.PAYMENT_CLASSIFICATION_NUMBER IN ('OR01', 'OR04', 'OR02', 'OR03')
                           AND S.SERVICE_DATE_ID BETWEEN v_qtr1_start_date and v_qtr1_end_date
                     ) 
                  WHERE
                     SERVICE_DATE_ID NOT IN (0, -1) --ignore blank service dates
                  GROUP BY
                     PAYMENT_CLASSIFICATION_NUMBER,
                     CLAIM_WCA_ID,
                     SERVICE_DATE_ID,
                     SERVICE_PROVIDER_ID,
                     AGENT_INSURER_ID,
                     WORK_STATUS_ID,
                     RTW_OUTCOME_FG
               )
         )
   );
COMMIT;