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 !!!!