Re: Complex Query
Date: 1996/04/04
Message-ID: <4jvgr7$k92_at_inet-nntp-gw-1.us.oracle.com>#1/1
Danny Hughes <hughes_at_phyplt.umsmed.edu> wrote:
>I need help with sql syntax for the following:
>I have a table called req that has the following fields, wo_number,
>room, enter_date, crew, description. I am trying to get a listing of
>possible duplicate work orders for each room. To be considered a
>possible duplicate, the work orders for each room will have a start date
>within two weeks of each other. I can get a complete listing by room and
>start date order but it consist of everything and all I am interested in
>is only the ones that have the start date within the 2 week period.
>example:
>wo_number room date
>wo1 100 1-10-96
>wo2 200 1-10-96
>wo3 200 1-15-96
>wo4 200 2-15-96
>wo5 300 2-16-96
>wo6 300 3-10-96
select wo_number, room, date
from T a
where exists
( select NULL
from T b
where b.room = a.room
and b.date between a.date-7 and a.date+7 )
/
This will always be a full scan on (T a) with (hopefully) an indexed read into (T b). That is assuming an index on T (room) and optimally T(room,date) to avoid the table access by rowid.
>in this example, the only ones I am interested in are wo2 and wo3 because
>they are the only ones that are within the two week period
>Thanks in advance
>Danny Hughes
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Thu Apr 04 1996 - 00:00:00 CEST