Re: tricky SQL query (currently handled through PL/SQL)

From: bung ho <bung_ho_at_hotmail.com>
Date: 11 Jun 2003 23:13:21 -0700
Message-ID: <567a1b1.0306112213.7b24e21a_at_posting.google.com>

jetlag11235_at_yahoo.com (Jetlag) wrote in message news:<2eeed00e.0306111351.149538f6_at_posting.google.com>...
> Ahh, I see where my example could lead to some confusion. An
> augmented example is included below. Note the re-occurrance of the
> value 1 for my_code. There are now two records in the output table
> with this value, and this is why a simple "group by" statement will
> not suffice. Apologies for the confusion.
>
> For example, consider this (new) example data in my_table_v1:
>
> 1 '01/01/03 00:00:00' 1
> 1 '01/01/03 00:00:01' 1
> 1 '01/01/03 00:00:04' 1
> 1 '01/01/03 00:00:05' 3
> 1 '01/01/03 00:00:09' 2
> 1 '01/01/03 00:00:15' 2
> 1 '01/01/03 00:00:20' 1
> 1 '01/01/03 00:00:24' 1
>
> This should become the following in my_table_v2:
>
> 1 '01/01/03 00:00:00' '01/01/03 00:00:04' 1
> 1 '01/01/03 00:00:05' '01/01/03 00:00:05' 3
> 1 '01/01/03 00:00:09' '01/01/03 00:00:15' 2
> 1 '01/01/03 00:00:20' '01/01/03 00:00:24' 1
>
> -- jetlag --

i'm not sure if this is any more 'elegant' than doing it in plsql, and it's hard to read, but i think it works:

select cd, mn, mx
from

   (select cd,

           decode(b, cd, '', dt) mn,
           decode(a, cd, lead(dt) over (order by dt), dt) mx
   from
      (select my_code cd,
              my_date dt,
              lag(my_code) over (order by my_date) b,
              lead(my_code) over (order by my_date) a
      from my_table_v1)

   where b != cd or a != cd or b is null or a is null) where mn is not null

i'm quite sure someone can come up with something better. Received on Thu Jun 12 2003 - 08:13:21 CEST