Hi All,
I would like to understand internal working of Lag function in Oracle, that might help in resolving my problem.
I have table like the below structure.
| P_ID | ENTRY_TIMESTAMP | SIZE | C_ID |
|---|
| 15287 | 01-NOV-15 05.45.00 PM | 30 | 92 |
| 15295 | 01-NOV-15 06.00.00 PM | 15 | 92 |
| 15306 | 01-NOV-15 06.00.00 PM | 1 | 92 |
Now when i write a query like below:
select size, TO_TIMESTAMP(TO_CHAR(ENTRY_TIMESTAMP, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') CURR_TIMESTAMP,
TO_TIMESTAMP(TO_CHAR(LAG(ENTRY_TIMESTAMP) OVER(PARTITION BY C_ID ORDER BY ENTRY_TIMESTAMP ASC), 'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI') LAG_TIMESTAMP
FROM Entry_log
where ENTRY_TIMESTAMP BETWEEN to_date('01-NOV-15 18.00.00','DD-MON-YY HH24.MI.SS') AND to_date('01-NOV-15 18.00.00','DD-MON-YY HH24.MI.SS')
Query will result in giving Lag_timestamp as 06.00.00 PM for size = 1 but will be NULL for size = 15.
Why is that Oracle never takes lag_timestamp = 06.00.00 PM for size = 15.
Could someone please explain this behaviour.
I am using Oracle 11g.
Thanks