Re: Can u count a certain # of things that occur over a range of dates?

I would be *very* carefule about running this query against a large table, but this worked for a small set of data where the number of consecutive readings required was five. This explains the 5 in the having clause of the query.

The query:

select a.id
from reading a, reading b
where a.id = b.id
and a.r_date = b.r_date-1
group by a.id
having count(*) >= 5-1

Used against the following data set:

        ID R_DATE R_VALUE
---------- --------- ----------

         1 01jan1999          1
         1 02jan1999          2
         1 03jan1999          3
         1 04jan1999          4
         1 05jan1999          5
         1 01feb1999          6
         1 03feb1999          7
         2 01jan1999          1
         2 02jan1999          2
         2 04jan1999          4
         2 05jan1999          5
         2 01feb1999          6
         2 03feb1999          7

Produced the following result:
        ID
----------
         1

Changing the 5 to 30, and verifying that the id and date columns are both indexed should produce the desired result. Be aware that this query could consume significant resources. It may be better to place this query inside a pl/sql block so that each id is evaluated one at a time.

hth

Chris

ramdan wrote:

> I need to know if: Is is possible to determine which meter_ids have more than
> 30 consective days of -99 readings in the period from 1/96 to 12/98?
>
> I have a 2,000,0000 row table with the following structure
>
> meter_id varchar
> reading_dt date
> read1-24 number
>
> meter_id reading_date read1 read2....read24
> 243344 01/01/1996 3 41 -99 4
> ..
> 243344 12/31/19998 4 -99 65 2
> 36456 01/01/1996 -99 -99 -99 -99
> ...
> 36456 12/31/19998 -99 -99 -99 3
>
> each meter_id can have up to 1096 rows (one for each day from 1/96 to 1/98)
> some will have a value of -99 which indicates a missing read.
>
> What I need to know is: Is is possible to determine which meter_ids have more
> than 30 consective days of -99 readings in the period from 1/96 to 12/98? I
> need at least 30 not less
Received on Wed May 05 1999 - 08:00:58 CDT