Re: Complex Query
From: Robert Walters <bobwal_at_infocom.com>
Date: 1996/04/05
Message-ID: <4k42c5$4q5_at_mips.infocom.com>#1/1
> 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
Date: 1996/04/05
Message-ID: <4k42c5$4q5_at_mips.infocom.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
>
> 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
select a.wo_number, a.room, a.date
from table a
where exists (select 'x'
from table b
where a.wo_number = b.wo_number
and a.room = b.room
and b.date between a.date-14 and a.date+ 14);
This should give both, however since I didn't test it, you may want to. Probably forgetting something obvious.
Bob Walters bobwal_at_infocom.comFlorists' Mutual Insurance Co.
"Calculus I can handle. It's adding and subtracting that gives me fits." Received on Fri Apr 05 1996 - 00:00:00 CEST