Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customer_records
(day date,
snapshot_day_dw_id number,
vend_loy_acct_dw_id number,
hub_cust_id number,
cur_loy_tier_dw_id number
);
INSERT INTO customer_records 
(day,snapshot_day_dw_id,vend_loy_acct_dw_id,hub_cust_id,cur_loy_tier_dw_id)
VALUES
(TO_DATE('9/24/2014', 'mm/dd/yyyy'),6266,71047795,476095,3103);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('10/1/2014', 'mm/dd/yyyy'),6273,71047795,476095,3103);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('10/8/2014', 'mm/dd/yyyy'),6280,71047795,476095,3103);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('10/15/2014', 'mm/dd/yyyy'),6287,71047795,476095,3103);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('10/22/2014', 'mm/dd/yyyy'),6291,71047795,476095,3102);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('10/29/2014', 'mm/dd/yyyy'),6330,71047795,476095,3102);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('11/05/2015', 'mm/dd/yyyy'),6351,71047795,476095,3102);
1 rows affected

INSERT INTO customer_records VALUES (TO_DATE('11/12/2015', 'mm/dd/yyyy'),6440,71047795,476095,3103);
1 rows affected

SELECT 
    c1.day day_from,
    COALESCE(c2.day, TO_DATE('2199-12-31', 'yyyy-mm-dd')) day_to,
    c1.VEND_LOY_ACCT_DW_ID,
    c1.HUB_CUST_ID, 
    c1.CUR_LOY_TIER_DW_ID
FROM customer_records c1
LEFT JOIN customer_records c2 
    ON  c2.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
    AND c2.HUB_CUST_ID         = c1.HUB_CUST_ID
    AND c2.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
    AND c2.DAY                 > c1.DAY
    AND NOT EXISTS (
        SELECT 1
        FROM customer_records c3
        WHERE
                c3.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
            AND c3.HUB_CUST_ID         = c1.HUB_CUST_ID
            AND c3.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
            AND c3.DAY                 > c1.DAY
            AND c3.DAY                 < c2.DAY
    )


DAY_FROM
DAY_TO
VEND_LOY_ACCT_DW_ID
HUB_CUST_ID
CUR_LOY_TIER_DW_ID


24-SEP-14
22-OCT-14
71047795
476095
3103

01-OCT-14
22-OCT-14
71047795
476095
3103

08-OCT-14
22-OCT-14
71047795
476095
3103

15-OCT-14
22-OCT-14
71047795
476095
3103

22-OCT-14
12-NOV-15
71047795
476095
3102

29-OCT-14
12-NOV-15
71047795
476095
3102

05-NOV-15
12-NOV-15
71047795
476095
3102

12-NOV-15
31-DEC-99
71047795
476095
3103


SELECT 
    ROW_NUMBER() OVER(ORDER BY c2.day) version,
    DECODE(c2.day, NULL, 'Y', 'N') current_flag,
    MIN(c1.day) day_from,
    COALESCE(c2.day, TO_DATE('2199-12-31', 'yyyy-mm-dd')) day_to,
    c1.VEND_LOY_ACCT_DW_ID,
    c1.HUB_CUST_ID, 
    c1.CUR_LOY_TIER_DW_ID
FROM customer_records c1
LEFT JOIN customer_records c2 
    ON  c2.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
    AND c2.HUB_CUST_ID         = c1.HUB_CUST_ID
    AND c2.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
    AND c2.DAY                 > c1.DAY
    AND NOT EXISTS (
        SELECT 1
        FROM customer_records c3
        WHERE
                c3.VEND_LOY_ACCT_DW_ID = c1.VEND_LOY_ACCT_DW_ID
            AND c3.HUB_CUST_ID         = c1.HUB_CUST_ID
            AND c3.CUR_LOY_TIER_DW_ID <> c1.CUR_LOY_TIER_DW_ID
            AND c3.DAY                 > c1.DAY
            AND c3.DAY                 < c2.DAY
    )
GROUP BY
    c1.VEND_LOY_ACCT_DW_ID, 
    c1.HUB_CUST_ID, 
    c1.CUR_LOY_TIER_DW_ID, 
    c2.day
ORDER BY
    day_from
    


VERSION
CURRENT_FLAG
DAY_FROM
DAY_TO
VEND_LOY_ACCT_DW_ID
HUB_CUST_ID
CUR_LOY_TIER_DW_ID


1
N
24-SEP-14
22-OCT-14
71047795
476095
3103

2
N
22-OCT-14
12-NOV-15
71047795
476095
3102

3
Y
12-NOV-15
31-DEC-99
71047795
476095
3103

DAY_FROM	DAY_TO	VEND_LOY_ACCT_DW_ID	HUB_CUST_ID	CUR_LOY_TIER_DW_ID
24-SEP-14	22-OCT-14	71047795	476095	3103
01-OCT-14	22-OCT-14	71047795	476095	3103
08-OCT-14	22-OCT-14	71047795	476095	3103
15-OCT-14	22-OCT-14	71047795	476095	3103
22-OCT-14	12-NOV-15	71047795	476095	3102
29-OCT-14	12-NOV-15	71047795	476095	3102
05-NOV-15	12-NOV-15	71047795	476095	3102
12-NOV-15	31-DEC-99	71047795	476095	3103