I come from a Microsoft SQL Server background. SQL Server's "windowed functions" are much less extensive than Oracle's analytic functions. I have a problem that I need to solve once in awhile and was wondering if there was a more straightforward way to solve with the additional analytic functions than what I'm doing now.
Start with a table change records changes and when they happen:
SQL> drop table status_change_log;
Table dropped.
SQL> create table status_change_log (id number(38) not null
2 , changed_at date not null
3 , status varchar2(25) not null
4 , constraint status_change_log_PK primary key (id, changed_at))
5 /
Table created.
SQL> insert into status_change_log
2 values (1, to_date('2011-01-01 10:00:00', 'YYYY-MM-DD HH24:Mi:SS'), 'NEW');
1 row created.
SQL> insert into status_change_log
2 values (1, to_date('2011-01-01 10:00:15', 'YYYY-MM-DD HH24:Mi:SS'), 'ACKNOWLEDGED');
1 row created.
SQL> insert into status_change_log
2 values (1, to_date('2011-01-01 10:00:20', 'YYYY-MM-DD HH24:Mi:SS'), 'WORKING');
1 row created.
SQL> insert into status_change_log
2 values (1, to_date('2011-01-01 11:00:00', 'YYYY-MM-DD HH24:Mi:SS'), 'SPOOLING');
1 row created.
SQL> insert into status_change_log
2 values (1, to_date('2011-01-01 11:00:10', 'YYYY-MM-DD HH24:Mi:SS'), 'DONE');
1 row created.
SQL> insert into status_change_log
2 values (2, to_date('2011-01-01 10:00:00', 'YYYY-MM-DD HH24:Mi:SS'), 'NEW');
1 row created.
SQL> insert into status_change_log
2 values (2, to_date('2011-01-01 10:00:05', 'YYYY-MM-DD HH24:Mi:SS'), 'ACKNOWLEDGED');
1 row created.
SQL> insert into status_change_log
2 values (2, to_date('2011-01-01 10:20:00', 'YYYY-MM-DD HH24:Mi:SS'), 'REQUESTING FOLLOW UP');
1 row created.
SQL> insert into status_change_log
2 values (2, to_date('2011-01-01 10:20:05', 'YYYY-MM-DD HH24:Mi:SS'), 'SUSPENDED');
1 row created.
SQL> commit;
Commit complete.
Then query to get the span of time a "status" was in affect:
SQL> with SCL as (select status_change_log.*
2 , row_number() over (partition by id order by changed_at) as RN
3 from status_change_log)
4 select begin_change.id
5 , begin_change.status
6 , begin_change.changed_at as start_date
7 , end_change.changed_at as end_date
8 from SCL begin_change
9 left outer join SCL end_change
10 on begin_change.id = end_change.id
11 and begin_change.RN = end_change.RN - 1
12 order by id, start_date
13 /
ID STATUS START_DATE END_DATE
---------- ------------------------- ------------------- -------------------
1 NEW 2011-01-01 10:00:00 2011-01-01 10:00:15
1 ACKNOWLEDGED 2011-01-01 10:00:15 2011-01-01 10:00:20
1 WORKING 2011-01-01 10:00:20 2011-01-01 11:00:00
1 SPOOLING 2011-01-01 11:00:00 2011-01-01 11:00:10
1 DONE 2011-01-01 11:00:10
2 NEW 2011-01-01 10:00:00 2011-01-01 10:00:05
2 ACKNOWLEDGED 2011-01-01 10:00:05 2011-01-01 10:20:00
2 REQUESTING FOLLOW UP 2011-01-01 10:20:00 2011-01-01 10:20:05
2 SUSPENDED 2011-01-01 10:20:05
9 rows selected.This gives the result I am looking for, but am curious if there is a better, more straight forward way to get the results in Oracle.
You are going to love lag and lead:
ops$tkyte%ORA11GR2> select id, status, changed_at start_date,
2 lead(changed_at) over (partition by id order by changed_at) as end_date
3 from status_change_log
4 /
ID STATUS START_DATE END_DATE
---------- ------------------------- -------------------- --------------------
1 NEW 01-jan-2011 10:00:00 01-jan-2011 10:00:15
1 ACKNOWLEDGED 01-jan-2011 10:00:15 01-jan-2011 10:00:20
1 WORKING 01-jan-2011 10:00:20 01-jan-2011 11:00:00
1 SPOOLING 01-jan-2011 11:00:00 01-jan-2011 11:00:10
1 DONE 01-jan-2011 11:00:10
2 NEW 01-jan-2011 10:00:00 01-jan-2011 10:00:05
2 ACKNOWLEDGED 01-jan-2011 10:00:05 01-jan-2011 10:20:00
2 REQUESTING FOLLOW UP 01-jan-2011 10:20:00 01-jan-2011 10:20:05
2 SUSPENDED 01-jan-2011 10:20:05
9 rows selected.