Re: Desperately need an SQL query for the following problem

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 12 Aug 2006 12:21:10 -0700
Message-ID: <1155410466.908085@bubbleator.drizzle.com>

Dave wrote:

> 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.

1. Please do NOT top post. Thank you.
2. If they can span multiple rows you need to look at analytic functions or use PL/SQL.

But from the sample data you posted I think you are making unwarranted assumptions about your data. Where are the integer values in the ROW column coming from.

3. And yet again ... please do not top post. Scroll to the bottom and post there if you wish help.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Aug 12 2006 - 14:21:10 CDT