In article <1147366346.517915.153600_at_i40g2000cwc.googlegroups.com>, "jonwat" <jonwaterhouse_at_mail.gov.nf.ca> wrote:
>
>
>Yes. This version is maybe a bit more general than you need (i.e. it
>will give you all periods, not just those that end on a particular
>date)
>
>create table test
>(id number(2),
> d date,
> v number);
>
> insert into test
> values (1,sysdate,20);
>
> insert into test
> values (1,sysdate-1,20);
>
> insert into test
> values (1,sysdate-2,15);
>
> insert into test
> values (1,sysdate-3,15);
>
> insert into test
> values (1,sysdate-4,5);
>
> insert into test
> values (1,sysdate-5,20);
>
>select id,max(d),count(*)
>FROM
>(select id,d,v,CASE WHEN v >= 10 THEN max(grp) OVER (partition by ID
>order by d desc)
> ELSE NULL
> END grp
>FROM
>(select id,d,v,CASE WHEN row_number() OVER (Partition by ID order by d
>desc)=1
> or lag(v) OVER (Partition by ID order by d
> desc) < 10
> THEN row_number() OVER (Partition by ID order by d
> desc)
> ELSE NULL
> END grp
>FROM test))
>WHERE grp IS NOT NULL
>GROUP BY ID,grp
>
>Jon
>
we want it from sysdate or from sysdate -1
Received on Thu May 11 2006 - 12:12:01 CDT
