CREATE TABLE SAMPLE_TEST
(
S_ID VARCHAR(10),
S_DATE DATE
)Table created.
INSERT INTO SAMPLE_TEST values ('A', to_date('01-FEB-2012','DD-MON-YYYY'))1 row(s) inserted.
INSERT INTO SAMPLE_TEST values ('A', to_date('14-MAR-2012','DD-MON-YYYY'))1 row(s) inserted.
INSERT INTO SAMPLE_TEST values ('A', to_date('28-MAR-2015','DD-MON-YYYY'))1 row(s) inserted.
INSERT INTO SAMPLE_TEST values ('A', to_date('28-AUG-2018','DD-MON-YYYY'))1 row(s) inserted.
SELECT *
FROM SAMPLE_TEST
| S_ID | S_DATE | A | 01-FEB-12 | A | 14-MAR-12 | A | 28-MAR-15 | A | 28-AUG-18 |
|---|
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-MAR-15 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | A | 28-MAR-15 | 28-MAR-13 | 28-MAR-15 | 28-MAR-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | A | 28-MAR-15 | 28-MAR-13 | 28-MAR-15 | 28-MAR-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-MAR-15 | 28-MAR-12 | 28-MAR-15 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 | 28-AUG-18 |
|---|
INSERT INTO SAMPLE_TEST values ('A', to_date('28-APR-2014','DD-MON-YYYY'))1 row(s) inserted.
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 | 28-AUG-18 | A | 28-APR-14 | 28-APR-12 | 28-APR-14 | 28-APR-11 | 01-FEB-12 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 | 28-AUG-18 | A | 28-APR-14 | 28-APR-12 | 28-APR-14 | 28-APR-11 | 01-FEB-12 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 | 28-AUG-18 | A | 28-APR-14 | 28-APR-12 | 28-APR-14 | 28-APR-11 | 01-FEB-12 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
ORDER BY Q.S_DATE
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | FIRST_DATE3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 28-APR-14 | 28-APR-12 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 | 28-AUG-18 |
|---|
SELECT Q.*,
ADD_MONTHS(Q.FIRST_DATE,-12*3) AS FIRST_DATE3,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=Q.S_ID
AND S1.S_DATE>=ADD_MONTHS(Q.FIRST_DATE,-12*3)
AND S1.S_DATE<=Q.FIRST_DATE
) AS FIRST_DATE_IN3
FROM(
SELECT
S.S_ID,
S.S_DATE,
ADD_MONTHS(S.S_DATE,-12*2) AS S_DATE2,
(
SELECT MIN(S1.S_DATE)
FROM SAMPLE_TEST S1
WHERE S1.S_ID=S.S_ID
AND S1.S_DATE>=ADD_MONTHS(S.S_DATE,-12*2)
AND S1.S_DATE<=S.S_DATE
) AS FIRST_DATE
FROM SAMPLE_TEST S
)Q
ORDER BY Q.S_DATE
| S_ID | S_DATE | S_DATE2 | FIRST_DATE | FIRST_DATE3 | FIRST_DATE_IN3 | A | 01-FEB-12 | 01-FEB-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 14-MAR-12 | 14-MAR-10 | 01-FEB-12 | 01-FEB-09 | 01-FEB-12 | A | 28-APR-14 | 28-APR-12 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-MAR-15 | 28-MAR-13 | 28-APR-14 | 28-APR-11 | 01-FEB-12 | A | 28-AUG-18 | 28-AUG-16 | 28-AUG-18 | 28-AUG-15 | 28-AUG-18 |
|---|