6

I have been working ot see the equivalent function for Oracle lead and lag function.

The oracle lead would look like

LEAD(col1.date,1,ADD_MONTHS(col1.DATE,12)) 
OVER(Partition By tab.a,tab.b,tab.c Order By tab.a)-1 END_DATE

LAG(col1.DATE + 7,1,col1.DATE-1) 
OVER(partition by tab.a,tab.b Order By tab.b) LAG_DATE

Any better idea

Rupesh Yadav
  • 14,336
  • 5
  • 53
  • 68
user708477
  • 147
  • 2
  • 4
  • 14

1 Answers1

9

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.

Rob Paller
  • 7,506
  • 23
  • 23
  • Thanks, I actually got the conversion using coalesce. COALESCE(MAX(CALENDAR_DATE) OVER(PARTITION BY 1 ORDER BY CALENDAR_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),ADD_MONTH(COL1.DATE,12)) AS Lag_ COALESCE(MIN(CALENDAR_DATE + INTERVAL '1' DAY) OVER(PARTITION BY 1 ORDER BY CALENDAR_DATE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),(COL1.DATE- INTERVAL '1' DAY)) AS Lead_ – user708477 Nov 15 '11 at 18:10
  • FYI - Teradata 16.x now includes LEAD and LAG natively. – Rob Paller May 06 '19 at 13:54