Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dates_test (
  id         NUMBER,
  value      int,
  occurrance DATE
);
BEGIN
-- march and september and october are missing for both IDs-
INSERT INTO dates_test VALUES (1, 10, TO_DATE('03-JAN-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('03-FEB-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('01-APR-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('01-MAY-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('01-JUN-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('01-JUL-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('01-AUG-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('07-NOV-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('07-DEC-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (1, 10, TO_DATE('07-JAN-2015','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('03-JAN-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('03-FEB-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('01-APR-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('01-MAY-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('01-JUN-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('01-JUL-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('01-AUG-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('07-NOV-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('07-DEC-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 10, TO_DATE('07-JAN-2015','DD-MON-YYYY'));
END;
/
1 rows affected

select * from dates_test;


ID
VALUE
OCCURRANCE


1
10
03-JAN-14

1
10
03-FEB-14

1
10
01-APR-14

1
10
01-MAY-14

1
10
01-JUN-14

1
10
01-JUL-14

1
10
01-AUG-14

1
10
07-NOV-14

1
10
07-DEC-14

1
10
07-JAN-15

2
10
03-JAN-14

2
10
03-FEB-14

2
10
01-APR-14

2
10
01-MAY-14

2
10
01-JUN-14

2
10
01-JUL-14

2
10
01-AUG-14

2
10
07-NOV-14

2
10
07-DEC-14

2
10
07-JAN-15


SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY OCCURRANCE) AS rn
    FROM dates_test A
    WHERE NOT EXISTS (
        SELECT *
        FROM dates_test B
        WHERE B.ID = A.ID AND EXTRACT(month FROM B.OCCURRANCE) = EXTRACT(month FROM A.OCCURRANCE - 1))


ID
OCCURRANCE
RN


2
01-APR-14
1

1
01-APR-14
2


WITH StartingPoints AS
(
    SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY OCCURRANCE) AS rn
    FROM dates_test A
    WHERE NOT EXISTS (
        SELECT *
        FROM dates_test B
        WHERE B.ID = A.ID AND EXTRACT(month FROM B.OCCURRANCE) = EXTRACT(month FROM A.OCCURRANCE) - 1)
),
EndingPoints AS
(
    SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY OCCURRANCE) AS rn
    FROM dates_test A
    WHERE NOT EXISTS (
        SELECT *
        FROM dates_test B
        WHERE B.ID = A.ID AND EXTRACT(month FROM B.OCCURRANCE) = EXTRACT(month FROM A.OCCURRANCE) - 1)
)
SELECT S.ID, EXTRACT(month FROM S.OCCURRANCE) AS start_range, EXTRACT(month FROM E.OCCURRANCE) AS end_range
FROM StartingPoints S
JOIN EndingPoints E ON E.ID = S.ID AND E.rn = S.rn;


ID
START_RANGE
END_RANGE


1
1
1

2
1
1

1
4
4

2
4
4

2
11
11

1
11
11

1
1
1

2
1
1

ID	VALUE	OCCURRANCE
1	10	03-JAN-14
1	10	03-FEB-14
1	10	01-APR-14
1	10	01-MAY-14
1	10	01-JUN-14
1	10	01-JUL-14
1	10	01-AUG-14
1	10	07-NOV-14
1	10	07-DEC-14
1	10	07-JAN-15
2	10	03-JAN-14
2	10	03-FEB-14
2	10	01-APR-14
2	10	01-MAY-14
2	10	01-JUN-14
2	10	01-JUL-14
2	10	01-AUG-14
2	10	07-NOV-14
2	10	07-DEC-14
2	10	07-JAN-15