Re: Complex Query

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
 

: 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

Hi Danny,

From what I could make out from your posting, try the following queries :

select a.WO_NUMBER,a.ROOM,a.DATE, b.WO_NUMBER,b.ROOM,b.DATE where a.ROOM=b.ROOM and a.WO_NUMBER <> b.WO_NUMBER and a.DATE < b.DATE and a.DATE+15 >b.date;

OR

select distinct a.WO_NUMBER,a.ROOM,a.DATE from REQ a, REQ b where a.ROOM=b.ROOM and a.WO_NUMBER <> b.WO_NUMBER and abs(a.DATE-b.DATE) < 15 order by ROOM, DATE;

Wish you luck.

Harinder Singh Received on Thu Apr 04 1996 - 00:00:00 CEST