Re: Need help on a query with analytics

From: kibeha <kibeha_at_post6.tele.dk>
Date: 23 Feb 2004 04:59:10 -0800
Message-ID: <444b180d.0402230459.13a7f72_at_posting.google.com>

fritschvince_at_yahoo.fr (FRITSCH Vincent) wrote in message news:<b220bc5e.0402222134.3ac12a71_at_posting.google.com>...
> What i want is this output, representing periods (with a begin and an
> end date) of successive status.
>
> customer status begin_ end_
> 1 1 01/01/03 21/01/03
> 1 0 21/01/03 03/08/03
> 1 1 03/08/03 01/12/03
> 1 0 01/12/03
>
>
> On a windows 2000 platform EE 8.1.7, i write this query :
>
> select customer,
> status,
> time begin_,
> lead(time) over (partition by customer order by time) end_
> from t
> order by customer, time
> /
>
> CUSTOMER S BEGIN_ END_
> --------- - -------- --------
> 1 1 01/01/03 21/01/03
> 1 0 21/01/03 03/08/03
> 1 1 03/08/03 09/09/03
> 1 1 09/09/03 01/12/03
> 1 0 01/12/03
>
> but this is not exactly what i search (lines 3 and 4 need to be merge)

Hi

What you're saying is, that the record of 09/09/03 can be ignored, as that record set the status of the customer to the same as it already was, right ? Then this will give you what you want :

SQL> select

  2     customer,
  3     status,
  4     time begin_,
  5     lead(time) over (partition by customer order by time) end_
  6  from (
  7     select
  8        t.*,
  9        case
 10           when status = lag(status) over (partition by customer order by
time) then 1
 11           else 0
 12        end samestatus
 13     from t

 14 )
 15 where samestatus = 0
 16 order by customer, time;

  CUSTOMER S BEGIN_ END_

---------- - -------- --------
         1 1 03-01-01 03-01-21
         1 0 03-01-21 03-08-03
         1 1 03-08-03 03-12-01
         1 0 03-12-01

Hope that helps.

/KiBeHa Received on Mon Feb 23 2004 - 13:59:10 CET