Dedup on consecutive data only

Transact-SQL
1

Hello Team,

I have below data

CREATE_DATE ROWID SUBROWID PHN_NR PHN_TYP
1/1/2024 10 1 222-223-1600 MOB
1/2/2024 10 1 222-223-1600 BSNS
1/3/2024 10 1 222-223-1600 MOB
1/4/2024 10 1 222-223-1600 MOB
1/5/2024 10 1 222-223-1600 MOB
1/6/2024 10 1 222-223-1600 MOB
1/7/2024 10 1 222-223-1600 MOB
1/1/2024 11 2 111-444-5555 BSNS
1/2/2024 11 2 111-444-5555 BSNS
1/3/2024 11 2 111-444-5555 MOB
1/4/2024 11 2 111-444-5555 BSNS

Expected output. Only the consecutive group by ROWID,SUBROWID,PHN_NR,PHN_TYP should be deduped as per CREATE_DATE. Below is expected output:

CREATE_DATE ROWID SUBROWID PHN_NR PHN_TYP
1/1/2024 10 1 222-223-1600 MOB
1/2/2024 10 1 222-223-1600 BSNS
1/3/2024 10 1 222-223-1600 MOB
1/1/2024 11 2 111-444-5555 BSNS
1/3/2024 11 2 111-444-5555 MOB
1/4/2024 11 2 111-444-5555 BSNS

WITH CTE AS
(
SELECT '01-01-2024' CREATE_DATE,'10' ROWID,'1' SUBROWID,'222-223-1600' PHN_NR,'MOB' PHN_TYP UNION ALL
SELECT '01-02-2024','10','1','222-223-1600','BSNS' UNION ALL
SELECT '01-03-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-04-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-05-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-06-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-07-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-01-2024','11','2','111-444-5555','BSNS' UNION ALL
SELECT '01-02-2024','11','2','111-444-5555','BSNS' UNION ALL
SELECT '01-03-2024','11','2','111-444-5555','MOB' UNION ALL
SELECT '01-04-2024','11','2','111-444-5555','BSNS'
)
SELECT * FROM CTE

Can you please help me with the SQL? This logic needs to be executed on the large table with more than 150 millions records..

2

you can make use of LAG() window function and compare with previous row value and then set a flag accordingly

WITH CTE AS
(
SELECT '01-01-2024' CREATE_DATE,'10' ROWID,'1' SUBROWID,'222-223-1600' PHN_NR,'MOB' PHN_TYP UNION ALL
SELECT '01-02-2024','10','1','222-223-1600','BSNS' UNION ALL
SELECT '01-03-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-04-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-05-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-06-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-07-2024','10','1','222-223-1600','MOB' UNION ALL
SELECT '01-01-2024','11','2','111-444-5555','BSNS' UNION ALL
SELECT '01-02-2024','11','2','111-444-5555','BSNS' UNION ALL
SELECT '01-03-2024','11','2','111-444-5555','MOB' UNION ALL
SELECT '01-04-2024','11','2','111-444-5555','BSNS'
),
CTE2 AS
(
SELECT *,
       flag = case when SUBROWID = LAG(SUBROWID) OVER (PARTITION BY ROWID ORDER BY CREATE_DATE) 
                   and  PHN_NR   = LAG(PHN_NR) OVER (PARTITION BY ROWID ORDER BY CREATE_DATE) 
                   and  PHN_TYP  = LAG(PHN_TYP) OVER (PARTITION BY ROWID ORDER BY CREATE_DATE) 
                   then 1 
                   else 0 
                   end
FROM   CTE
)
SELECT *
FROM   CTE2
WHERE  flag = 0
ORDER BY ROWID, CREATE_DATE

see db<>fiddle demo

3

Thank you khtan !!!!

4 
Hi 

Hope this helps 

WITH CTE AS
(
    SELECT CREATE_DATE, ROWID, SUBROWID, PHN_NR, PHN_TYP,
           ROW_NUMBER() OVER (PARTITION BY ROWID, SUBROWID, PHN_NR ORDER BY CREATE_DATE) AS rn,
           LAG(PHN_TYP) OVER (PARTITION BY ROWID, SUBROWID, PHN_NR ORDER BY CREATE_DATE) AS prev_PHN_TYP
    FROM 
    (
        SELECT '01-01-2024' CREATE_DATE, '10' ROWID, '1' SUBROWID, '222-223-1600' PHN_NR, 'MOB' PHN_TYP UNION ALL
        SELECT '01-02-2024', '10', '1', '222-223-1600', 'BSNS' UNION ALL
        SELECT '01-03-2024', '10', '1', '222-223-1600', 'MOB' UNION ALL
        SELECT '01-04-2024', '10', '1', '222-223-1600', 'MOB' UNION ALL
        SELECT '01-05-2024', '10', '1', '222-223-1600', 'MOB' UNION ALL
        SELECT '01-06-2024', '10', '1', '222-223-1600', 'MOB' UNION ALL
        SELECT '01-07-2024', '10', '1', '222-223-1600', 'MOB' UNION ALL
        SELECT '01-01-2024', '11', '2', '111-444-5555', 'BSNS' UNION ALL
        SELECT '01-02-2024', '11', '2', '111-444-5555', 'BSNS' UNION ALL
        SELECT '01-03-2024', '11', '2', '111-444-5555', 'MOB' UNION ALL
        SELECT '01-04-2024', '11', '2', '111-444-5555', 'BSNS'
    ) AS SourceData
)
SELECT CREATE_DATE, ROWID, SUBROWID, PHN_NR, PHN_TYP
FROM CTE
WHERE PHN_TYP <> prev_PHN_TYP OR prev_PHN_TYP IS NULL
ORDER BY ROWID, SUBROWID, CREATE_DATE;