SQL & PL/SQL

I have following data set:

CREATE TABLE APPS.T1
(
  ROW_NUM               NUMBER,
  EFFECTIVE_START_DATE  DATE                    NOT NULL,
  EFFECTIVE_END_DATE    DATE                    NOT NULL,
  STATUS                VARCHAR2(30 BYTE)
);


SET DEFINE OFF;
Insert into APPS.T1
   (ROW_NUM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, STATUS)
 Values
   (1, TO_DATE('07/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/06/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VAC');
Insert into APPS.T1
   (ROW_NUM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, STATUS)
 Values
   (2, TO_DATE('03/20/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VAC');
Insert into APPS.T1
   (ROW_NUM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, STATUS)
 Values
   (3, TO_DATE('08/06/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VAC');
Insert into APPS.T1
   (ROW_NUM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, STATUS)
 Values
   (4, TO_DATE('08/23/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VAC');
Insert into APPS.T1
   (ROW_NUM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, STATUS)
 Values
   (5, TO_DATE('08/27/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/27/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VAC');
COMMIT;


SELECT * FROM APPS.T1

   ROW_NUM EFFECTIVE EFFECTIVE STATUS                        
---------- --------- --------- ------------------------------
         1 01-JUL-09 06-SEP-09 VAC                           
         2 20-MAR-11 31-MAR-11 VAC                           
         3 06-AUG-11 22-AUG-11 VAC                           
         4 23-AUG-11 26-AUG-11 VAC                           
         5 27-AUG-11 27-AUG-11 VAC                           

5 rows selected.

My requirement was that row number 3, 4 and 5 be grouped and treated as a single vacation record such that

effective_start_date = 06-AUG-2011 and
effective_end_date = 27-AUG-2011


For this I wrote a query:

SELECT effective_start_date,
       effective_end_date,
       CASE
          WHEN LAG (effective_end_date, 1) OVER (ORDER BY row_num) + 1 = effective_start_date
             THEN 0
          WHEN LEAD (effective_start_date, 1) OVER (ORDER BY row_num) - 1 = effective_end_date
             THEN 0
          ELSE 1
       END row_num
  FROM (SELECT * FROM T1)

Now the data returned looks like:

EFFECTIVE EFFECTIVE    ROW_NUM
--------- --------- ----------
01-JUL-09 06-SEP-09          1
20-MAR-11 31-MAR-11          1
06-AUG-11 22-AUG-11          0
23-AUG-11 26-AUG-11          0
27-AUG-11 27-AUG-11          0

5 rows selected.

Now I can easily use MIN(effective_start_date) and MAX(effective_start_date) group by ROW_NUM to achieve the desired results

SELECT   MIN (effective_start_date) start_dt,
         MAX (effective_start_date) end_dt,
         row_num
    FROM (SELECT effective_start_date,
                 effective_end_date,
                 CASE
                    WHEN LAG (effective_end_date, 1) OVER (ORDER BY row_num) + 1 = effective_start_date
                       THEN 0
                    WHEN LEAD (effective_start_date, 1) OVER (ORDER BY row_num) - 1 = effective_end_date
                       THEN 0
                    ELSE 1
                 END row_num
            FROM (SELECT *
                    FROM t1))
GROUP BY row_num
  HAVING row_num = 0
UNION
SELECT effective_start_date start_dt,
       effective_start_date end_dt,
       row_num
  FROM (SELECT effective_start_date,
               effective_end_date,
               CASE
                  WHEN LAG (effective_end_date, 1) OVER (ORDER BY row_num) + 1 = effective_start_date
                     THEN 0
                  WHEN LEAD (effective_start_date, 1) OVER (ORDER BY row_num) - 1 = effective_end_date
                     THEN 0
                  ELSE 1
               END row_num
          FROM (SELECT *
                  FROM t1))
 WHERE row_num = 1

START_DT  END_DT       ROW_NUM
--------- --------- ----------
01-JUL-09 01-JUL-09          1
20-MAR-11 20-MAR-11          1
06-AUG-11 27-AUG-11          0

3 rows selected.

All done BUT the problem is that there may be several groups of consecutive rows like this. In that case each group should be identified distinctly for GROUP BY clause to work as expected.

I want to assign a unique number to each occurence of such group.

How can I achieve this? Any ideas?

Regards,

Faraz

Edited by: faanwar on May 10, 2012 3:36 PM