Thanks again for your help.
After reviewing my data more thoroughly I found some anomalies that were not accouted for. My goal is to actually only keep records that have the first set of X number of consecutive pairs. The code you provided worked perfect but when I tried to reduce the set to where pair_seq=1, for some IDs it returned records with 1 pair or 2 consecutive pairs when those IDs had 3 consecutive pairs where pair_seq=2 or more. I provided 3 example cases below. Any help is much apprieciated.
The sort order of the data is: order by ID, STARTDATE, IntervalType desc;
Example 1, for first X=3 consecutive pairs (pair=1,1,2,2,3,3), I would only keep records in GREEN where ID = 1, 2 & 3 & drop records in RED
ID
DataStartDate
IntervalType
pair
StartDate
gap
pair_seq
1
2-Apr-14
REST
1
3-Apr-14
38
1
1
2-Apr-14
ACTIVE
1
3-Apr-14
0
1
1
2-Apr-14
REST
2
4-Apr-14
1
1
1
2-Apr-14
ACTIVE
2
4-Apr-14
0
1
1
2-Apr-14
REST
3
5-Apr-14
1
1
1
2-Apr-14
ACTIVE
3
5-Apr-14
0
1
1
2-Apr-14
REST
4
6-Apr-14
1
1
1
2-Apr-14
ACTIVE
4
6-Apr-14
0
1
1
2-Apr-14
REST
5
7-Apr-14
1
1
1
2-Apr-14
ACTIVE
5
7-Apr-14
0
1
2
26-Mar-13
REST
1
27-Mar-13
-14
1
2
26-Mar-13
ACTIVE
1
27-Mar-13
0
1
2
1-Apr-13
REST
1
2-Apr-13
6
2
2
1-Apr-13
ACTIVE
1
2-Apr-13
0
2
2
1-Apr-13
REST
2
3-Apr-13
1
2
2
1-Apr-13
ACTIVE
2
3-Apr-13
0
2
2
1-Apr-13
REST
3
4-Apr-13
1
2
2
1-Apr-13
ACTIVE
3
4-Apr-13
0
2
2
1-Apr-13
REST
4
5-Apr-13
1
2
2
1-Apr-13
ACTIVE
4
5-Apr-13
0
2
2
1-Apr-13
REST
5
6-Apr-13
1
2
2
1-Apr-13
ACTIVE
5
6-Apr-13
0
2
2
1-Apr-13
REST
6
7-Apr-13
1
2
2
1-Apr-13
ACTIVE
6
7-Apr-13
0
2
2
1-Apr-13
REST
7
8-Apr-13
1
2
2
1-Apr-13
ACTIVE
7
8-Apr-13
0
2
3
12-Sep-14
REST
1
13-Sep-14
-5
1
3
12-Sep-14
ACTIVE
1
13-Sep-14
0
1
3
12-Sep-14
REST
2
14-Sep-14
1
1
3
12-Sep-14
ACTIVE
2
14-Sep-14
0
1
3
16-Oct-14
REST
1
17-Oct-14
33
2
3
16-Oct-14
ACTIVE
1
17-Oct-14
0
2
3
16-Oct-14
REST
1
20-Oct-14
3
3
3
16-Oct-14
ACTIVE
1
20-Oct-14
0
3
3
16-Oct-14
REST
2
21-Oct-14
1
3
3
16-Oct-14
ACTIVE
2
21-Oct-14
0
3
3
16-Oct-14
REST
3
22-Oct-14
1
3
3
16-Oct-14
ACTIVE
3
22-Oct-14
0
3
3
16-Oct-14
REST
4
23-Oct-14
1
3
3
16-Oct-14
ACTIVE
4
23-Oct-14
0
3
Example 2, for first X=2 consecutive pairs (pair=1,1,2,2), I would only keep records in GREEN where ID = 1, 2 & 3 & drop records in RED
ID
DataStartDate
IntervalType
pair
StartDate
gap
pair_seq
1
2-Apr-14
REST
1
3-Apr-14
38
1
1
2-Apr-14
ACTIVE
1
3-Apr-14
0
1
1
2-Apr-14
REST
2
4-Apr-14
1
1
1
2-Apr-14
ACTIVE
2
4-Apr-14
0
1
1
2-Apr-14
REST
3
5-Apr-14
1
1
1
2-Apr-14
ACTIVE
3
5-Apr-14
0
1
1
2-Apr-14
REST
4
6-Apr-14
1
1
1
2-Apr-14
ACTIVE
4
6-Apr-14
0
1
1
2-Apr-14
REST
5
7-Apr-14
1
1
1
2-Apr-14
ACTIVE
5
7-Apr-14
0
1
2
26-Mar-13
REST
1
27-Mar-13
-14
1
2
26-Mar-13
ACTIVE
1
27-Mar-13
0
1
2
1-Apr-13
REST
1
2-Apr-13
6
2
2
1-Apr-13
ACTIVE
1
2-Apr-13
0
2
2
1-Apr-13
REST
2
3-Apr-13
1
2
2
1-Apr-13
ACTIVE
2
3-Apr-13
0
2
2
1-Apr-13
REST
3
4-Apr-13
1
2
2
1-Apr-13
ACTIVE
3
4-Apr-13
0
2
2
1-Apr-13
REST
4
5-Apr-13
1
2
2
1-Apr-13
ACTIVE
4
5-Apr-13
0
2
2
1-Apr-13
REST
5
6-Apr-13
1
2
2
1-Apr-13
ACTIVE
5
6-Apr-13
0
2
2
1-Apr-13
REST
6
7-Apr-13
1
2
2
1-Apr-13
ACTIVE
6
7-Apr-13
0
2
2
1-Apr-13
REST
7
8-Apr-13
1
2
2
1-Apr-13
ACTIVE
7
8-Apr-13
0
2
3
12-Sep-14
REST
1
13-Sep-14
-5
1
3
12-Sep-14
ACTIVE
1
13-Sep-14
0
1
3
12-Sep-14
REST
2
14-Sep-14
1
1
3
12-Sep-14
ACTIVE
2
14-Sep-14
0
1
3
16-Oct-14
REST
1
17-Oct-14
33
2
3
16-Oct-14
ACTIVE
1
17-Oct-14
0
2
3
16-Oct-14
REST
1
20-Oct-14
3
3
3
16-Oct-14
ACTIVE
1
20-Oct-14
0
3
3
16-Oct-14
REST
2
21-Oct-14
1
3
3
16-Oct-14
ACTIVE
2
21-Oct-14
0
3
3
16-Oct-14
REST
3
22-Oct-14
1
3
3
16-Oct-14
ACTIVE
3
22-Oct-14
0
3
3
16-Oct-14
REST
4
23-Oct-14
1
3
3
16-Oct-14
ACTIVE
4
23-Oct-14
0
3
Example 3, for first X=4 consecutive pairs (pair=1,1,2,2,3,3,4,4), I would only keep records in GREEN where ID = 1, 2 & 3 & drop records in RED
ID
DataStartDate
IntervalType
pair
StartDate
gap
pair_seq
1
2-Apr-14
REST
1
3-Apr-14
38
1
1
2-Apr-14
ACTIVE
1
3-Apr-14
0
1
1
2-Apr-14
REST
2
4-Apr-14
1
1
1
2-Apr-14
ACTIVE
2
4-Apr-14
0
1
1
2-Apr-14
REST
3
5-Apr-14
1
1
1
2-Apr-14
ACTIVE
3
5-Apr-14
0
1
1
2-Apr-14
REST
4
6-Apr-14
1
1
1
2-Apr-14
ACTIVE
4
6-Apr-14
0
1
1
2-Apr-14
REST
5
7-Apr-14
1
1
1
2-Apr-14
ACTIVE
5
7-Apr-14
0
1
2
26-Mar-13
REST
1
27-Mar-13
-14
1
2
26-Mar-13
ACTIVE
1
27-Mar-13
0
1
2
1-Apr-13
REST
1
2-Apr-13
6
2
2
1-Apr-13
ACTIVE
1
2-Apr-13
0
2
2
1-Apr-13
REST
2
3-Apr-13
1
2
2
1-Apr-13
ACTIVE
2
3-Apr-13
0
2
2
1-Apr-13
REST
3
4-Apr-13
1
2
2
1-Apr-13
ACTIVE
3
4-Apr-13
0
2
2
1-Apr-13
REST
4
5-Apr-13
1
2
2
1-Apr-13
ACTIVE
4
5-Apr-13
0
2
2
1-Apr-13
REST
5
6-Apr-13
1
2
2
1-Apr-13
ACTIVE
5
6-Apr-13
0
2
2
1-Apr-13
REST
6
7-Apr-13
1
2
2
1-Apr-13
ACTIVE
6
7-Apr-13
0
2
2
1-Apr-13
REST
7
8-Apr-13
1
2
2
1-Apr-13
ACTIVE
7
8-Apr-13
0
2
3
12-Sep-14
REST
1
13-Sep-14
-5
1
3
12-Sep-14
ACTIVE
1
13-Sep-14
0
1
3
12-Sep-14
REST
2
14-Sep-14
1
1
3
12-Sep-14
ACTIVE
2
14-Sep-14
0
1
3
16-Oct-14
REST
1
17-Oct-14
33
2
3
16-Oct-14
ACTIVE
1
17-Oct-14
0
2
3
16-Oct-14
REST
1
20-Oct-14
3
3
3
16-Oct-14
ACTIVE
1
20-Oct-14
0
3
3
16-Oct-14
REST
2
21-Oct-14
1
3
3
16-Oct-14
ACTIVE
2
21-Oct-14
0
3
3
16-Oct-14
REST
3
22-Oct-14
1
3
3
16-Oct-14
ACTIVE
3
22-Oct-14
0
3
3
16-Oct-14
REST
4
23-Oct-14
1
3
3
16-Oct-14
ACTIVE
4
23-Oct-14
0
3
... View more