Re: Compare current and next row data

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 2 Jun 2005 05:13:21 -0700
Message-ID: <1117714401.163391.48870@g43g2000cwa.googlegroups.com>

Yea, analytics will do this for you. Here is an example:-

Create a table similar to yours:-

>create table t1 (id integer, dtm date, value integer);

Insert some values:-

>insert into t1 values (1, sysdate-1,100);
>insert into t1 values (1, sysdate-2,200);
>insert into t1 values (1, sysdate-3,300);
>insert into t1 values (2, sysdate-4,400);
>insert into t1 values (2, sysdate-5,500);
>insert into t1 values (2, sysdate-6,600);

So, you want to find each row, with the date in the next row:-

select id,

	   dtm this_row_time,
	   value,
	   lead(dtm) over (order by dtm desc)  next_row_time

from t1
order by dtm desc;

        ID THIS_ROW_ VALUE NEXT_ROW_

---------- --------- ---------- ---------
         1 01-JUN-05        100 31-MAY-05
         1 31-MAY-05        200 30-MAY-05
         1 30-MAY-05        300 29-MAY-05
         2 29-MAY-05        400 28-MAY-05
         2 28-MAY-05        500 27-MAY-05
         2 27-MAY-05        600

6 rows selected.

Or if you want to group the results up, with those that have the same ID (maby this is items in a time line associated with an order or something)

select id,

         dtm this_row_time,
         value,
         lead(dtm) over (partition by id order by dtm desc)

next_row_time
from t1
order by dtm desc;

        ID THIS_ROW_ VALUE NEXT_ROW_

---------- --------- ---------- ---------
         1 01-JUN-05        100 31-MAY-05
         1 31-MAY-05        200 30-MAY-05
         1 30-MAY-05        300
         2 29-MAY-05        400 28-MAY-05
         2 28-MAY-05        500 27-MAY-05
         2 27-MAY-05        600

6 rows selected.

To get the difference between the dates simply subtract this_row_date from next_row_date (i think the result is an integer in days).

Hope this helps ... Received on Thu Jun 02 2005 - 07:13:21 CDT