Help with Query

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)

Code:

---

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.

This seems to do it:

VB Code:

Declare @Tbl Table (MastId int, HistId int, Loc varchar(2), StartDate datetime, ExpiryDate datetime)
 
Set NoCount On
 
Insert into @Tbl values (518362,870355,'A','2004-05-12 09:22:00','2004-05-12 12:16:00')
Insert into @Tbl values (518362,870571,'AS','2004-05-12 12:16:00','2004-05-14 14:44:00')
Insert into @Tbl values (518362,871773,'AS','2004-05-14 14:44:00','2004-05-19 16:55:00')
Insert into @Tbl values (518362,873742,'AS','2004-05-19 16:55:00','2004-05-20 13:00:00')
Insert into @Tbl values (518362,874379,'AS','2004-05-20 13:00:00','2004-05-21 11:30:00')
Insert into @Tbl values (518362,874931,'A','2004-05-21 11:30:00','2004-05-23 14:04:00')
Insert into @Tbl values (518362,874977,'AS','2004-05-23 14:04:00','2004-05-27 00:06:00')
Insert into @Tbl values (518362,876304,'L','2004-05-27 00:06:00','2004-05-27 00:07:00')
Insert into @Tbl values (518362,876306,'AS','2004-05-27 00:07:00','2004-07-08 10:53:00')
Insert into @Tbl values (518362,892807,'AS','2004-07-08 10:53:00','2004-07-13 09:08:00')
Insert into @Tbl values (518362,894863,'AS','2004-07-13 09:08:00','2004-07-17 23:58:00')
Insert into @Tbl values (518362,897112,'AS','2004-07-17 23:58:00','2004-07-20 11:46:00')
Insert into @Tbl values (518362,897698,'A','2004-07-20 11:46:00','2004-07-20 12:03:00')
Insert into @Tbl values (518362,897706,'A','2004-07-20 12:03:00','2004-07-20 12:05:00')
Insert into @Tbl values (518362,897708,'XS','2004-07-20 12:05:00','2004-07-20 14:03:29')
Insert into @Tbl values (518362,897889,'XS','2004-07-20 14:03:28','2004-07-22 21:38:00')
Insert into @Tbl values (518362,899357,'YS','2004-07-22 21:38:00','2004-07-22 21:40:00')
Insert into @Tbl values (518362,899358,'YS','2004-07-22 21:40:00','2004-07-22 21:52:00')
Insert into @Tbl values (518362,899360,'XS','2004-07-22 21:52:00','2004-07-24 10:11:00')
Insert into @Tbl values (518362,899729,'XS','2004-07-24 10:11:00','2004-07-25 16:17:00')
Insert into @Tbl values (518362,899781,'XS','2004-07-25 16:17:00','2004-07-25 16:40:00')
Insert into @Tbl values (518362,900064,'XS','2004-07-25 16:40:00','2004-07-31 10:28:00')
Insert into @Tbl values (518362,902112,'XS','2004-07-31 10:28:00','2004-08-18 14:50:00')
Insert into @Tbl values (518362,907449,'ZS','2004-08-18 14:50:00','2004-08-19 23:12:00')
Insert into @Tbl values (518362,908088,'ZS','2004-08-19 23:12:00','2004-10-11 23:22:00')
Insert into @Tbl values (518362,926964,'ZS','2004-10-11 23:22:00','2004-10-14 19:07:00')
Insert into @Tbl values (518362,928149,'A','2004-10-14 19:07:00','2004-11-10 22:30:00')
Insert into @Tbl values (518362,939535,'AS','2004-11-10 22:30:00','2004-11-11 11:01:26')
 
Select MastId,StartDate From @Tbl Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'
                        or ExpiryDate between '2004-08-01' and '2004-08-15'
                        or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15'))
-- Get our record that we will pivot on
 
Select Max(StartDate) From @Tbl T1 Where T1.StartDate<(Select StartDate From @Tbl Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'
                                or ExpiryDate between '2004-08-01' and '2004-08-15'
                                or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))
                        and T1.Loc not like '%S'
-- and the bottom boundary (use that pivot query as the sub-query)
 
Select Min(StartDate) From @Tbl T1 Where T1.StartDate>(Select StartDate From @Tbl Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'
                                or ExpiryDate between '2004-08-01' and '2004-08-15'
                                or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))
                        and T1.Loc not like '%S'
-- and the top boundary (use that pivot query as the sub-query)
 
Select * From @Tbl T2 Where Loc like '%S'
                and StartDate>(Select Max(StartDate) From @Tbl T1 Where T1.StartDate<(Select StartDate From @Tbl
                                Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'
                                or ExpiryDate between '2004-08-01' and '2004-08-15'
                                or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))
                        and T1.Loc not like '%S')
                and StartDate<(Select Min(StartDate) From @Tbl T1 Where T1.StartDate>(Select StartDate From @Tbl
                                Where Loc like '%S' and (StartDate between '2004-08-01' and '2004-08-15'
                                or ExpiryDate between '2004-08-01' and '2004-08-15'
                                or (StartDate<'2004-08-01' and ExpiryDate>'2004-08-15')))
                        and T1.Loc not like '%S')
-- now the rows between our boundaries (use the bottom and top boundary queries)

returns...

VB Code:

MastId      StartDate                                              
----------- ------------------------------------------------------ 
518362      2004-07-31 10:28:00.000
 
                                                       
------------------------------------------------------ 
2004-07-20 12:03:00.000
 
                                                       
------------------------------------------------------ 
2004-10-14 19:07:00.000
 
MastId      HistId      Loc  StartDate                                              ExpiryDate                                             
----------- ----------- ---- ------------------------------------------------------ ------------------------------------------------------ 
518362      897708      XS   2004-07-20 12:05:00.000                                2004-07-20 14:03:29.000
518362      897889      XS   2004-07-20 14:03:28.000                                2004-07-22 21:38:00.000
518362      899357      YS   2004-07-22 21:38:00.000                                2004-07-22 21:40:00.000
518362      899358      YS   2004-07-22 21:40:00.000                                2004-07-22 21:52:00.000
518362      899360      XS   2004-07-22 21:52:00.000                                2004-07-24 10:11:00.000
518362      899729      XS   2004-07-24 10:11:00.000                                2004-07-25 16:17:00.000
518362      899781      XS   2004-07-25 16:17:00.000                                2004-07-25 16:40:00.000
518362      900064      XS   2004-07-25 16:40:00.000                                2004-07-31 10:28:00.000
518362      902112      XS   2004-07-31 10:28:00.000                                2004-08-18 14:50:00.000
518362      907449      ZS   2004-08-18 14:50:00.000                                2004-08-19 23:12:00.000
518362      908088      ZS   2004-08-19 23:12:00.000                                2004-10-11 23:22:00.000
518362      926964      ZS   2004-10-11 23:22:00.000                                2004-10-14 19:07:00.000

You will have to deal with the top and bottom boundary queries returning nothing - when there are no boundaries. In that case the boundary is DATEADD(dd,-1,{the min date in our %S group) and DATEADD(1,dd,{the max date in our %S group})

Thanks szlamany. I did some test and unfortunately the query did not work. By changing the date range (Jul 12 - Aug 20), the subquery returns multiple records which causes an error. I added Max to the subquery but the dates in range 876306 to 897112 were not returned along with the other range.

Oh well. Now it's time to enjoy the weekend.