OraQA

The following SQL pattern can be used to find the earliest day that is at least one year after the prior calculated date in a SQL statement. There are several ways to solve this problem, the alternative approach in 10G would be to use the SQL Model Clause.

CREATE TABLE T8
(
  ID           NUMBER,
  REGIST_DATE  DATE
);

INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/01/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/03/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/03/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/03/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/03/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/04/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/03/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '07/04/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '01/05/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '01/10/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '02/15/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '03/31/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO T8 ( ID, REGIST_DATE ) VALUES (
1,  TO_Date( '09/30/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 

SQL> select * from t8 order by id, regist_date;

ID REGIST_DA
-- ---------
1 01-JUL-02  Y ( Flag : Y   Start of the registration)
1 03-JUL-02  N ( Flag : N Less than 1 year after the first ‘Y’ )
1 03-JUL-02  N ( Less than 1 year )
1 03-JUL-02  N ( Less than 1 year )
1 03-JUL-02  N ( Less than 1 year )
1 04-JUL-02  N ( Less than 1 year )
1 03-JUL-03  Y (A Year After First Y and this is the earliest date)
1 04-JUL-03  N (A Year After First Y, but not the earliest date)
1 05-JAN-04  N (Less than 1 year after the Second 'Y')
1 10-JAN-04  N (Less than 1 year after the Second 'Y')
1 15-FEB-04  N (Less than 1 year after the Second 'Y')
1 31-MAR-04  N (Less than 1 year after the Second 'Y')
1 30-SEP-04  Y (A Year After Second 'Y' and this is the earliest date)   

SELECT t8.ID id, regist_date REG_DATE,
      CASE WHEN INSTR(inline_view.path,TO_CHAR(regist_date))>0 THEN 'Y'
           ELSE 'N' END OUTCOME
  FROM t8,
  (SELECT id, path
     FROM (SELECT id, len, longest, numstr, path,
                  MIN(CASE WHEN len = longest THEN numstr END)
                  OVER (PARTITION BY id) smallest
            FROM (SELECT id,path, numstr,LENGTH(path)-
                         LENGTH(REPLACE(path,'+','')) len,
                         MAX(LENGTH(path)-LENGTH(REPLACE(path,'+','')))
                         OVER (PARTITION BY id) longest
                  FROM(SELECT id,rn,
                       REG_DATE,sys_connect_by_path(REG_DATE,'+')path,
                       TO_NUMBER(REPLACE(sys_connect_by_path(rn,
                                 '/'),'/')) numstr
                        FROM
                         (SELECT ID id, regist_date REG_DATE,
                                 row_number() OVER (PARTITION BY ID
                                 ORDER BY regist_date) rn
                            FROM  t8
                         )
                        START WITH rn = 1
                        CONNECT BY
                        MONTHS_BETWEEN(REG_DATE,PRIOR REG_DATE)>=12
                        AND PRIOR id = id
                     )
                )
		    WHERE len = longest
         )
         WHERE numstr = smallest
   ) inline_view
 WHERE t8.ID  = inline_view.id
 ORDER BY ID,REG_DATE ;

        ID REG_DATE  O
---------- --------- -
         1 01-JUL-02 Y
         1 03-JUL-02 N
         1 03-JUL-02 N
         1 03-JUL-02 N
         1 03-JUL-02 N
         1 04-JUL-02 N
         1 03-JUL-03 Y
         1 04-JUL-03 N
         1 05-JAN-04 N
         1 10-JAN-04 N
         1 15-FEB-04 N
         1 31-MAR-04 N
         1 30-SEP-04 Y                                                          

13 rows selected.

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.