Sharon,
> I have a query that is not returning the desired results. I want the
results to be a list of permit_id's held between 2 dates, but the results I'm
getting is only those permit_id's that start on date_from ('27-06-02') and
finish on date_to ('11-07-02'), I also want the permit_id's returned for
those permits that are held within that time period, for example a permit
that starts on 20-06-02 and finishes on 04-07-02. I've tried all sorts of
variations but I'm still not getting the right results, anyone have any
ideas?
>
> SELECT permit_id
> FROM faps_permit
> WHERE person_id = (select person_id from person where person_id = 100)
> AND cancel_permit is null
> AND location = 'Kent Forest'
> AND ( (date_from >= '27-06-02' AND date_to <= '11-07-02')
> OR (date_from <= '27-06-02' AND date_to >= '11-07-02') )
> ORDER BY permit_id ;
According to the conditions above, you'll be getting any permit_id where from
and to are both between the two dates, or from and to are both outside the
two dates. Not what you want.
Actually, what you want is the OVERLAPS function:
SELECT permit_id
FROM faps_permit
WHERE person_id = (select person_id from person where person_id = 100)
AND cancel_permit is null
AND location = 'Kent Forest'
AND OVERLAPS ('27-06-02, '11-07-02', date_from, date_to)
ORDER BY permit_id ;
This will give you all permits that started, ended or were held during the
named period.
-Josh Berkus