By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
stayid varchar(20),
cid varchar(20),
arrival date,
departure date,
country varchar(20),
state varchar(20),
pid varchar(20),
email varchar(20),
phone int
);
insert into mytable values
('ab1','a101','2023-01-01','2023-04-05','US','ohio','ly1','101@gmail.com','12345'),
('ab2','a101','2023-04-05','2023-04-06','US','ohio','ly1','101@gmail.com','12345'),
('ab7','a106','2023-01-20','2023-04-26','US','Texas','ly6','106@gmail.com','87468'),
('ab8','a107','2023-01-20','2023-04-26','US','Texas','ly7','107@gmail.com','55555'),
('ab10','a109','2023-01-20','2023-04-28','US','Texas','ly9','109@gmail.com','55555');
5 rows affected
select *, DATEDIFF(day, departure, lead(departure, 1, departure) over (partition by cid order by departure)) as date_diff
from mytable;
| stayid | cid | arrival | departure | country | state | pid | phone | date_diff | |
|---|---|---|---|---|---|---|---|---|---|
| ab1 | a101 | 2023-01-01 | 2023-04-05 | US | ohio | ly1 | 101@gmail.com | 12345 | 1 |
| ab2 | a101 | 2023-04-05 | 2023-04-06 | US | ohio | ly1 | 101@gmail.com | 12345 | 0 |
| ab7 | a106 | 2023-01-20 | 2023-04-26 | US | Texas | ly6 | 106@gmail.com | 87468 | 0 |
| ab8 | a107 | 2023-01-20 | 2023-04-26 | US | Texas | ly7 | 107@gmail.com | 55555 | 0 |
| ab10 | a109 | 2023-01-20 | 2023-04-28 | US | Texas | ly9 | 109@gmail.com | 55555 | 0 |
-- top be removed
with cte as (
select *,
lead(stayid) over (partition by cid order by departure) as lead_stayid,
DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff
from mytable
)
select *
from cte
where date_diff is not null and date_diff < 30
| stayid | cid | arrival | departure | country | state | pid | phone | lead_stayid | date_diff | |
|---|---|---|---|---|---|---|---|---|---|---|
| ab1 | a101 | 2023-01-01 | 2023-04-05 | US | ohio | ly1 | 101@gmail.com | 12345 | ab2 | 1 |
with cte as (
select *,
lead(stayid) over (partition by cid order by departure) as lead_stayid,
DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff
from mytable
)
delete from mytable
Where stayid in (
select stayid
from cte
where date_diff is not null and date_diff < 30
)
1 rows affected
select *
from mytable
| stayid | cid | arrival | departure | country | state | pid | phone | |
|---|---|---|---|---|---|---|---|---|
| ab2 | a101 | 2023-04-05 | 2023-04-06 | US | ohio | ly1 | 101@gmail.com | 12345 |
| ab7 | a106 | 2023-01-20 | 2023-04-26 | US | Texas | ly6 | 106@gmail.com | 87468 |
| ab8 | a107 | 2023-01-20 | 2023-04-26 | US | Texas | ly7 | 107@gmail.com | 55555 |
| ab10 | a109 | 2023-01-20 | 2023-04-28 | US | Texas | ly9 | 109@gmail.com | 55555 |