Hi, we are automating a metadata testing where we need to find the interval of the data in type 2 tables.
Here the logic is the end date column becomes the exact next row's effective column. So the difference in time interval is 0. Some tables will have difference of 1 sec, 1 min and 1 day. I need to have a query which will find out this time interval difference.
Can anyone help me.
|ID||Effective date column||End date column|
|100||9/28/2013 08:39:14.235824||10/3/2013 06:49:29.484928|
|100||10/3/2013 06:49:29.484928||10/3/2013 06:52:16.913392|
|100||10/3/2013 06:52:16.913392||12/31/9999 23:59:59.000000|
Getting the previous row's value is a simple OLAP function:
OVER (PARTITION BY id
ORDER BY eff_date
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) DAY(4) TO SECOND
You probably want to use that in a QUALIFY ... <> INTERVAL '0' SECOND to find gaps.
Thank You so much Dieter.. It was very helpful.