Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Desperately need an SQL query for the following problem

Re: Desperately need an SQL query for the following problem

From: Dave <drathnow_at_telus.net>
Date: 12 Aug 2006 10:23:33 -0700
Message-ID: <1155403413.687485.42870@m73g2000cwd.googlegroups.com>


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.

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;
>
> You could also write it like this if you prefer:
> SELECT
> MYTABLE.*
> FROM
> MYTABLE M,
> QUERY_TABLE QT
> WHERE
> QT.STARTDATE BETWEEN M.STARTDATE AND M.ENDDATE
> UNION
> SELECT
> MYTABLE.*
> FROM
> MYTABLE M,
> QUERY_TABLE QT
> WHERE
> QT.ENDDATE BETWEEN M.STARTDATE AND M.ENDDATE;
>
> In my example, QUERY_TABLE (QT) is a table that represents the queries
> that you are passing in to be evaluated. If there will be no query
> table, you may need to write the SQL statement similar to the following
> for the first query:
> SELECT
> *
> FROM
> MYTABLE M
> WHERE
> '01-JAN-2001' BETWEEN M.STARTDATE AND M.ENDDATE
> OR '22-JAN-2001' BETWEEN M.STARTDATE AND M.ENDDATE;
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Sat Aug 12 2006 - 12:23:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US