Re: Desperately need an SQL query for the following problem

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

It seems that you are looking for records where there is any overlap between the ranges (startDate,enddate) and ( qstartDate,qenddate).

Then all you need is:

startDate<=qEndDate and EndDate>=qStartDate

You should "draw" the different possibilities of how the ranges could overlap and examine them to see why the above condition will always work:

1. (startDate,enddate) completely included in ( qstartDate,qenddate)

qsd                                                 qed
                 sd               ed

2) (qstartDate,qenddate) completely included in ( startDate,enddate)

sd                                                 ed
                 qsd               qed

3) (qstartDate,qenddate) overlaps with ( startDate,enddate) "on the left"

qsd                         qed
                 sd                         ed

4) (qstartDate,qenddate) overlaps with ( startDate,enddate) "on the right"

            qsd                         qed
sd                         ed

Now look at the possibilities of how the ranges would NOT overlap, and you can see that the condition will not be true. The two possibilities are

qsd            qed
                                sd                  ed

and

sd            ed
                                qsd                  qed

FM Received on Tue Aug 22 2006 - 13:22:16 CDT