Menu
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
930
Syntax: LEAD(expression_column, n , default_value) OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2]) expression_column -> Lag value to be checked for column n -> Value of "n" will be` 1 if not specified, will be equivalent to "n PRECEDING" default_value -> If no row satisfy the window condition then default_value ...
The LAG function accesses data from a row preceding the current row at a specified offset value in a window group, while the LEAD function returns data from a row following the current row at a specified offset value in a window group.
Description. The Oracle/PLSQL LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.
The LAG and LEAD functions are OLAP ranking functions that return the value of their expression argument for the row at a specified offset from the current row within the current window partition.
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.
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
