Today I came across one interesting question at Oracle Forum

Dear experts!
Please help me with this kind of query!
Suppose that I have a table like the following to keep track of moving path of an item

location datetime

l1 2006/10/01
l1 2006/10/20
l1 2006/11/01
l2 2006/11/03
l2 2006/11/19
l1 2006/11/28
l1 2006/12/10

How can I figure which location the object used to be at and the duration it stayed there over time, so that the result of the query look like this:
location date_start date_end
l1 2006/10/01 2006/10/20
l2 2006/11/03 2006/11/19
l1 2006/11/28 2006/12/10

Thank you very much!

——————————————————————–

I though lot about it, and gave him following answer:

Ok first I created one table:

CREATE TABLE TEMP1234(
location VARCHAR2(6),
date_start DATE
);
ALTER SESSION SET NLS_DATE_FORMAT=’YYYY/MM/DD’;

INSERT into TEMP1234 values (’l1′, ‘2006/10/01′);
INSERT into TEMP1234 values (’l1′, ‘2006/10/20′);
INSERT into TEMP1234 values (’l1′, ‘2006/11/01′);
INSERT into TEMP1234 values (’l2′, ‘2006/11/03′);
INSERT into TEMP1234 values (’l2′, ‘2006/11/19′);
INSERT into TEMP1234 values (’l1′, ‘2006/11/28′);
INSERT into TEMP1234 values (’l1′, ‘2006/12/10′);

COMMIT;

Now once it is done, now issue following SQL.

SELECT location, date_start, (MAX(date_start) OVER ( order by date_start rows
between current row and 1 following)) as date_end
FROM TEMP1234
ORDER BY date_start
/

Result will be as follows:
LOCATI DATE_START DATE_END
—— ———- ———-
l1 2006/10/01 2006/10/20
l1 2006/10/20 2006/11/01
l1 2006/11/01 2006/11/03
l2 2006/11/03 2006/11/19
l2 2006/11/19 2006/11/28
l1 2006/11/28 2006/12/10
l1 2006/12/10 2006/12/10

7 rows selected.
I hope this will solve your problem.