By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
column1 varchar(20),
Date date
)
insert into mytable values
('Value1', '2023-02-01'),
('Value2', '2023-02-01'),
('Value2', '2023-02-02'),
('Value3', '2023-02-02'),
('Value2', '2023-02-03'),
('Value4', '2023-02-03');
6 rows affected
with cte as (
select column1, DATEADD(day, 1, min(Date)) AS Date
from mytable
where Date < (select max(Date) from mytable)
group by column1
having count(1) = 1
union
select column1, Date
from mytable
),
cte2 as (
select c.*, iif(t.Date is not null, 'Added', 'Closed') as status
from cte c
left join mytable t on t.column1 = c.column1 and t.Date = c.Date
)
select column1, min(Date) as Date, status
from cte2
group by column1, status
order by min(Date), column1;
| column1 | Date | status |
|---|---|---|
| Value1 | 2023-02-01 | Added |
| Value2 | 2023-02-01 | Added |
| Value1 | 2023-02-02 | Closed |
| Value3 | 2023-02-02 | Added |
| Value3 | 2023-02-03 | Closed |
| Value4 | 2023-02-03 | Added |