Need to find difference between the effective column of the next row and expiration column of the previous row

General
Enthusiast

Need to find difference between the effective column of the next row and expiration column of the previous row

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
Tags (1)
2 REPLIES
Junior Contributor

Re: Need to find difference between the effective column of the next row and expiration column of the previous row

Getting the previous row's value is a simple OLAP function:

eff_date -
MIN(end_date)
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.

Dieter

Enthusiast

Re: Need to find difference between the effective column of the next row and expiration column of the previous row

Thank You so much Dieter.. It was very helpful.