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 Fri Aug 11 2006 - 21:46:15 CDT
