Re: Desperately need an SQL query for the following problem

Dave wrote:
> Charles Hooper wrote:
> > Dave wrote:
> > > I don't write a lot of SQL queries but have been able to do most of the
> > > work with what little I know. This one, however, has me stumpped.
> > > Here's my problem:
> > >
> > > I have records in a table that contain a start date and an end date.
> > > I need to select records from this table that fall within a date range.
> > >
> > > The date range could cover a single,multiple or no rows depending on
> > > the
> > > range being queried. Example:
> > >
> > > My Table:
> > > Row StartDate EndDate
> > > 1 1-Jan-2001 15-Jan-2001
> > > 2 16-Jan-2001 2-Feb-2001
> > > 3 3-Feb-2001 15-Feb-2001
> > > 4 16-Feb-2001 5-Mar-2001
> > > 5 6-Mar-2001 15-Mar-2001
> > >
> > > Here are some possible queries:
> > >
> > > Query StartDate EndDate ExpectedRow(s)
> > > 1 1-Jan-2001 22-Jan-2001 1,2
> > > 2 17-Jan-2001 25-Jan-2001 2
> > > 3 17-Jan-2001 8-Mar-2001 2,3,4,5
> > > 4 17-Mar-2001 1-Apr-2001 5
> > > 5 1-Apr-2001 20-Apr-2001 nothing
> > >
> > > I've been trying a lot of different selects but nothing is working.
> > >
> > > Can anyone help?
> > >
> > > Dave.
> >
> > If I understand your logic correctly, you want to return any row from
> > MyTable where the queried StartDate falls between the MyTable StartDate
> > and EndDate OR the queried EndDate falls between the MyTable
> > StartDate and EndDate. If this is correct, the SQL statement is fairly
> > easy to contruct:
> > SELECT
> > MYTABLE.*
> > FROM
> > MYTABLE M,
> > QUERY_TABLE QT
> > WHERE
> > QT.STARTDATE BETWEEN M.STARTDATE AND M.ENDDATE
> > OR QT.ENDDATE BETWEEN M.STARTDATE AND M.ENDDATE;
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> No, it's not quite that simple. The query dates could fall between a
> single row's startDate and endDate or could span multiple rows.
>
> I've been beating this one with lots of different combinations and this
> WHERE clause seems to work (sorry if this is poorly formatted).
> qStartDate and qEndDate is range of data that I'm interested in:
>
> (((startDate <= qStartDate AND startDate <= qEndDate) AND (endDate >=
> qStartDate AND endDate >= qEndDate))
> OR ((startDate <= qStartDate AND endDate >= qStartDate) OR (startDate
> >= qStartDate AND endDate <= qEndDate) OR (startDate <= qEndDate AND endDate >= qEndDate))
> OR ((startDate <= qStartDate AND startDate <= qEndDate) AND (endDate >=
> qStartDate AND endDate >= qEndDate)))
>
> It seems to work but there must be a simpler way to do this.
>
> Dave.
>

I executed the logic of the SQL statement that I posted and came up with the following:

Query   StartDate   EndDate      ExpectedRow(s)
1       1-Jan-2001  22-Jan-2001  1,2
2       17-Jan-2001 25-Jan-2001  2
3       17-Jan-2001 8-Mar-2001   2,5
4       17-Mar-2001 1-Apr-2001   nothing
5       1-Apr-2001  20-Apr-2001  nothing

For query #3 above, you also want to include 3 and 4. For query #4 above, no date range appears to satisfy this query. To fix the results of query #3, you will need to add a couple more OR statements to the WHERE clause:
SELECT
  MYTABLE.*
FROM
  MYTABLE M,
  QUERY_TABLE QT
WHERE
  QT.STARTDATE BETWEEN M.STARTDATE AND M.ENDDATE   OR QT.ENDDATE BETWEEN M.STARTDATE AND M.ENDDATE   OR M.STARTDATE BETWEEN QT.STARTDATE AND QT.ENDDATE   OR M.ENDDATE BETWEEN QT.STARTDATE AND QT.ENDDATE; The above basically states that if a start date in one of the tables falls between the start and end dates in the other table or if an end date in one of the tables falls between the start and end dates in the other table, then the row should be considered a match.

Query #4 still has no match.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Aug 12 2006 - 15:13:49 CDT