Re: select rows from multiple date ranges.

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Fri, 8 Apr 2005 22:49:20 +0000 (UTC)
Message-ID: <d371pg$5n6$1@klatschtante.init7.net>

On 2005-04-08, Piyush <piyush_parmar_at_calpers.ca.gov> wrote:
> Hi,
>
> I've been using basic SQL for a while, but this one has me puzzled. I
> am hoping somebody out there knows how to accomplish this. I need to
> view log entries from a table that exist between multiple date ranges.
> example:
>
> row timestamp ref_item item_des
> --- ---------- --------- ---------
> 1 4/7/2005 xxx xxxx
> 2 4/7/2005 process initialized.
> 3 4/7/2005 xxx xxxx
> 4 4/7/2005 xxx xxxx
> 5 4/7/2005 xxx xxxx
> 6 4/7/2005 process completed.
> 7 4/7/2005 xxx xxxx
> 8 4/7/2005 xxx xxxx
> 9 4/8/2005 process initialized.
> 10 4/8/2005 xxx xxxx
> 11 4/8/2005 xxx xxxx
> 12 4/8/2005 xxx xxxx
> 13 4/8/2005 process completed.
> 14 4/9/2005 xxx xxxx
>
> I need to select all log entries that occur between the
> process/initalized and process/completed, but nothing outside that
> range. so in this example, i would only select rows 2-6 and rows 9-13.
> any ideas?

This should be possible with analytical functions:

create table test_table (
  row_ number primary key,
  timestamp date,
  ref_item varchar2(4),
  item_des varchar2(11)
);

insert into test_table values( 1, sysdate - 14/24, 'xxx',  'xxxx');
insert into test_table values( 2, sysdate - 13/24, 'proc', 'init');
insert into test_table values( 3, sysdate - 12/24, 'xxx',  'xxxx');
insert into test_table values( 4, sysdate - 11/24, 'xxx',  'xxxx');
insert into test_table values( 5, sysdate - 10/24, 'xxx',  'xxxx');
insert into test_table values( 6, sysdate -  9/24, 'proc', 'comp');
insert into test_table values( 7, sysdate -  8/24, 'xxx',  'xxxx');
insert into test_table values( 8, sysdate -  7/24, 'xxx',  'xxxx');
insert into test_table values( 9, sysdate -  6/24, 'proc', 'init');
insert into test_table values(10, sysdate -  5/24, 'xxx',  'xxxx');
insert into test_table values(11, sysdate -  4/24, 'xxx',  'xxxx');
insert into test_table values(12, sysdate -  3/24, 'xxx',  'xxxx');
insert into test_table values(13, sysdate -  2/24, 'proc', 'comp');
insert into test_table values(14, sysdate -  1/24, 'xxx',  'xxxx');

select
  row_
from (
  select
    row_,
    timestamp,
    sum(on_off) over (order by timestamp) sum_on_off,     ref_item,
    item_des
  from
  (
    select

      row_,
      timestamp,
          lag(item_des,1) 
          over(order by timestamp) x,
      case
        when 
          ref_item = 'proc' and
          item_des = 'init'                then 1
        when 
          lag(ref_item,1) 
          over(order by timestamp)= 'proc' and
          lag(item_des,1) 
          over(order by timestamp)= 'comp' then 1
        else 0
      end on_off,
      ref_item,
      item_des
    from
      test_table

  )
)
where
  mod(sum_on_off,2) = 1
;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Fri Apr 08 2005 - 17:49:20 CDT