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