Teradata Equivalent for Lead and Lag Function of Oracle

Teradata equivalent for lead and lag function of oracle

I believe you can take the following SQL as a basis and modify it to meet your needs:

SELECT CALENDAR_DATE
     , MAX(CALENDAR_DATE)
       OVER(PARTITION BY 1 ORDER BY CALENDAR_DATE
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS Lag_ --Yesterday
     , MIN(CALENDAR_DATE)
            OVER(PARTITION BY 1 ORDER BY CALENDAR_DATE
            ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS Lead_ --Tomorrow
FROM SysCalendar.CALENDAR
WHERE year_of_calendar = 2011
  AND month_of_year = 11

NULL is returned when there is no record before or after and can be addressed with a COALESCE as necessary.

EDIT In Teradata 16.00 LAG/LEAD functions were introduced.

Lead Lag function not working on Teradata 15.0 .Any alternative method that could give me similar results

IIRC (I might not), LEAD/LAG wasn't fully implemented until 16. So try:

MAX(COALESCE(TENURE_DAYS,0)) OVER ( PARTITION BY CUSTID_MASTER ORDER BY CUSTID_MASTER ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)

The pattern is: MAX(<column>) OVER (PARTITION BY <column(s)> ORDER BY <column(s)> ROWS BETWEEN 1 <PRECEDING (Lag)/FOLLOWING (Lead)> AND 1 <PRECEDING (Lag)/FOLLOWING (Lead)>)

How to use lead or Lag for a Group in oracle or any other analytic function to get desired result?

You can use the below query to get the desired result:

with 
CTE1(ID, VALUE) AS
(select distinct id, 
 value from table1 
 order by id
),
CTE2 AS
(
SELECT A.ID, 
  A.VALUE, 
  LEAD(A.VALUE) OVER(ORDER BY A.ID) LEAD_VALUE 
FROM CTE1 A
)
SELECT A.*, B.LEAD_VALUE 
FROM TABLE1 A, CTE2 B
WHERE A.ID = B.ID;

DB_FIDDLE

Oracle SQL LAG function return the wrong calculation in Time

Your first logic is fine. You should also consider day differences. But for the sole purpose of giving you the solution you want try below.

Solution

WITH MAIN
     AS (SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-03 16:48:48', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 11:51:44', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 11:57:24', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 15:14:21', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'WD72120' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 15:17:17', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 15:28:43', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 15:30:00', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-05 15:42:44', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-07 10:28:18', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL
         UNION ALL
         SELECT '93K-P007' AS EQP_ID,
                'GC00495' LOT_ID,
                'LOAD_LOT' ACTIVITY,
                TO_DATE ('2020-08-07 10:33:31', 'YYYY-MM-DD HH24:MI:SS')
                   AS DATE_TIME
           FROM DUAL)
SELECT EQP_ID,
               LOT_ID,
               ACTIVITY,
               DATE_TIME,
               TO_CHAR(
               LPAD(ABS(EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR(LAG (DATE_TIME, 1 ,DATE_TIME)OVER (PARTITION BY EQP_ID ORDER BY DATE_TIME),'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS')) -
               EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR(DATE_TIME,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS'))) ,2,'0')||':'||
               LPAD(ABS(EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR(LAG (DATE_TIME, 1 ,DATE_TIME)OVER (PARTITION BY EQP_ID ORDER BY DATE_TIME),'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS')) -
               EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR(DATE_TIME,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS'))) ,2,'0')||':'||
               LPAD(ABS(EXTRACT(SECOND FROM TO_TIMESTAMP(TO_CHAR(LAG (DATE_TIME, 1 ,DATE_TIME)OVER (PARTITION BY EQP_ID ORDER BY DATE_TIME),'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS')) -
               EXTRACT(SECOND FROM TO_TIMESTAMP(TO_CHAR(DATE_TIME,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS'))),2,'0') 
               ) AS DIFF
          FROM MAIN
         WHERE EQP_ID = '93K-P007'

---

Related Topics