CREATE TABLE T1
AS
SELECT 'A' ITEM,
'L' LOC,
TO\_DATE ('01-MAR-15', 'DD-MON-RR') EFF,
TO\_DATE ('31-Mar-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A' ITEM,
'L' LOC,
TO\_DATE ('07-MAR-15', 'DD-MON-RR') EFF,
TO\_DATE ('15-Mar-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A' ITEM,
'L' LOC,
TO\_DATE ('12-MAR-15', 'DD-MON-RR') EFF,
TO\_DATE ('18-APR-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A1' ITEM,
'L1' LOC,
TO\_DATE ('12-JUN-15', 'DD-MON-RR') EFF,
TO\_DATE ('18-JUN-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A1' ITEM,
'L1' LOC,
TO\_DATE ('16-JUN-15', 'DD-MON-RR') EFF,
TO\_DATE ('25-JUN-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A2' ITEM,
'L2' LOC,
TO\_DATE ('20-SEP-15', 'DD-MON-RR') EFF,
TO\_DATE ('25-SEP-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A2' ITEM,
'L2' LOC,
TO\_DATE ('28-SEP-15', 'DD-MON-RR') EFF,
TO\_DATE ('25-OCT-15', 'DD-MON-RR') DISC
FROM DUAL
UNION ALL
SELECT 'A3' ITEM,
'L3' LOC,
TO\_DATE ('16-JAN-15', 'DD-MON-RR') EFF,
TO\_DATE ('25-FEB-15', 'DD-MON-RR') DISC
FROM DUAL;
Present Output
Expected Output
| Item | Loc | Eff | Disc |
| A | L | 01-MAR-2015 | 07-MAR-2015 |
| A | L | 07-MAR-2015 | 12-MAR-2015 |
| A | L | 12-MAR-2015 | 15-MAR-2015 |
| A | L | 15-MAR-2015 | 18-MAR-2015 |
| A | L | 18-MAR-2015 | 31-MAR-2015 |
| A1 | L1 | 12-JUN-2015 | 16-JUN-2015 |
| A1 | L1 | 16-JUN-2015 | 18-JUN-2015 |
| A1 | L1 | 18-JUN-2015 | 25-JUN-2015 |
| A2 | L2 | 20-SEP-2015 | 25-SEP-2015 |
| A2 | L2 | 28-SEP-2015 | 25-OCT-2015 |
| A3 | L3 | 16-JAN-2015 | 25-FEB-2015 |
Logic :
For a combination of Item and Location, if there are any overlapping dates, rearrange dates.
As shown in above example,
For Item A and Location L, rows 2 and 3 are overlapped with row one and row 2 and row 3 are partially overlapped.
For Item A1 and Location L1 rows are partially overlapped
For item A2 and Location L2 rows are not overlapped so output should be as is
For Item A3 and Location L3 rows are not overlapped so output should be as is
I tried below query and only able to get partial results
SELECT T1.*,
CASE
WHEN DISC >
LEAD (EFF, 1, DISC)
OVER (PARTITION BY ITEM, LOC ORDER BY ITEM, LOC, EFF)
THEN
LEAD (EFF, 1, DISC)
OVER (PARTITION BY ITEM, LOC ORDER BY ITEM, LOC, EFF)
ELSE
DISC
END
NEW\_DISC
FROM T1
ORDER BY ITEM, LOC, EFF;