Help with Query

[brucevde]

This query has me stumped. The DB is Oracle but I cannot use stored procedures, temp tables etc only sql statements.

I need to return the first Start Date and last Expiry date from a Range of records if any one of the records within the range meets the following criteria (user will supply a From and To date period).

Location Like '%S' And
(Start Date is within the period OR
Expiry date is within the period OR
Start Date is < From date and Expiry Date is > To date)

For example, user enters Aug 1 2004 to Aug 15 2004
History Id 902112 satisfies the criteria so the range of records would be history id's 897708 - 926964. The range is based on the Location, ie go back from 902112 until a Location is NOT like '%S' and of course go forward until a Location is NOT like '%S'. So the query would return

Master Id = 518362, Start Date = 2004-07-20 12:05:00, Expiry Date = 2004-10-14 19:07:00

If the user enters Jul 20 2004 to Jul 21 2004, 3 records match the criteria - 897112, 897708, 897889. However 897708, 897889 are in the same range so the query would return 2 records.

Master Id = 518362, Start Date = 2004-05-27 00:07:00, Expiry Date = 2004-07-20 11:46:00 (range would be 876306 to 897112)
Master Id = 518362, Start Date = 2004-07-20 12:05:00, Expiry Date = 2004-10-14 19:07:00 (range would be 897708 to 926964)

MastId	HistId	Loc	Start Date		Expiry Date
518362	870355	A	2004-05-12 09:22:00	2004-05-12 12:16:00
518362	870571	AS	2004-05-12 12:16:00	2004-05-14 14:44:00
518362	871773	AS	2004-05-14 14:44:00	2004-05-19 16:55:00
518362	873742	AS	2004-05-19 16:55:00	2004-05-20 13:00:00
518362	874379	AS	2004-05-20 13:00:00	2004-05-21 11:30:00
518362	874931	A	2004-05-21 11:30:00	2004-05-23 14:04:00
518362	874977	AS	2004-05-23 14:04:00	2004-05-27 00:06:00
518362	876304	L	2004-05-27 00:06:00	2004-05-27 00:07:00
518362	876306	AS	2004-05-27 00:07:00	2004-07-08 10:53:00
518362	892807	AS	2004-07-08 10:53:00	2004-07-13 09:08:00
518362	894863	AS	2004-07-13 09:08:00	2004-07-17 23:58:00
518362	897112	AS	2004-07-17 23:58:00	2004-07-20 11:46:00
518362	897698	A	2004-07-20 11:46:00	2004-07-20 12:03:00
518362	897706	A	2004-07-20 12:03:00	2004-07-20 12:05:00
518362	897708	XS	2004-07-20 12:05:00	2004-07-20 14:03:29
518362	897889	XS	2004-07-20 14:03:28	2004-07-22 21:38:00
518362	899357	YS	2004-07-22 21:38:00	2004-07-22 21:40:00
518362	899358	YS	2004-07-22 21:40:00	2004-07-22 21:52:00
518362	899360	XS	2004-07-22 21:52:00	2004-07-24 10:11:00
518362	899729	XS	2004-07-24 10:11:00	2004-07-25 16:17:00
518362	899781	XS	2004-07-25 16:17:00	2004-07-25 16:40:00
518362	900064	XS	2004-07-25 16:40:00	2004-07-31 10:28:00
518362	902112	XS	2004-07-31 10:28:00	2004-08-18 14:50:00
518362	907449	ZS	2004-08-18 14:50:00	2004-08-19 23:12:00
518362	908088	ZS	2004-08-19 23:12:00	2004-10-11 23:22:00
518362	926964	ZS	2004-10-11 23:22:00	2004-10-14 19:07:00
518362	928149	A	2004-10-14 19:07:00	2004-11-10 22:30:00
518362	939535	AS	2004-11-10 22:30:00	2004-11-11 11:01:26

I just need ideas. I have tried queries with subqueries with subqueries but always find cases where information is not being returned.