Quote:
what I want to achieve with the CTE is check my Table and look for duplicate that are say with 20min and delete one them. how can I achieve that with just one CTE query? thank you in advance
I don't think you can do it with a single cte. I also wouldn't use a cross apply but instead use a Dimension table that contained all the possible 20 minute "slots" in my data. You can keep this as a cte or store the information in a temporary table. I would then have a query (or cte) that gets which "slot" each of my data rows is in, then do the partitioned row number on that. You say you only want to delete one so in my example below I only delete where the partitioned row number
equals 2
declare @demo table (COL_CALL varchar(10), COL_TIME_ON datetime);
insert into @demo (COL_CALL, COL_TIME_ON) values
('2E0FNU', '2022-08-25 10:00:00'),
('2E0FNU', '2022-08-25 10:19:00'),
('2E0FNU', '2022-08-25 10:21:00'),
('2E0FNU', '2022-08-25 10:58:00'),
('2E0FNU', '2022-08-25 10:59:00'),
('2E0FNU', '2022-08-25 11:00:00'),
('2E0FNU', '2022-08-25 12:00:00'),
('2E0FNU', '2022-08-25 12:10:00');
select * from @demo
declare @start datetime = (select min(COL_TIME_ON) from @demo);
declare @end datetime = (select dateadd(minute, 20, max(COL_TIME_ON)) from @demo);
WITH q AS
(
SELECT @start AS datum, 1 as slot
UNION ALL
SELECT dateadd(minute, 20, datum), slot + 1
FROM q
WHERE dateadd(minute, 20, datum) < dateadd(minute, 20, @end)
), cte1 as
(
SELECT datum, slot, lead(datum) over (order by slot) as nextdatum
FROM q
), cte2 as
(
select *, row_number() over (partition by slot order by slot) as rowc
from cte1 a
inner join @DEMO b on b.COL_TIME_ON between a.datum and a.nextdatum
)
delete
from cte2
where rowc = 2
select * from @demo