delete the conseqitive row based on the date difference of next row

Question

Hi,

I have a table like below
create table #MyTable (ID int, GName nvarchar(100), GDate date)

insert into #MyTable values (1001, 'ABC', '2021-01-01')
insert into #MyTable values (1001, 'ABC', '2021-01-03')
insert into #MyTable values (1002, 'DEF', '2021-01-01')
insert into #MyTable values (1001, 'ABC', '2021-01-11')
insert into #MyTable values (1001, 'ABC', '2021-01-22')
insert into #MyTable values (1001, 'ABC', '2021-01-24')
insert into #MyTable values (1001, 'ABC', '2021-02-04')
insert into #MyTable values (1002, 'DEF', '2021-01-05')
insert into #MyTable values (1002, 'DEF', '2021-01-23')

select * from #MyTable
drop table #MyTable

My requirement is to remove row if the difference of next row date is less 10 days

for example

1. first i have to group the data based on ID and Gname
2. if the first row ID and Name matched with second row ID and Name then i have to get the date difference. if the date difference is lessthan 10 days then i have to delete the 2nd row. again
   i have to compare the first row with third row like wise i have to continue.

my expected result is like below

create table #Result (ID int, GName nvarchar(100), GDate date, DayDiff int)
insert into #Result values (1001,'ABC','2021-01-01',1)
insert into #Result values (1001,'ABC','2021-01-11',10)
insert into #Result values (1001,'ABC','2021-01-22',11)
insert into #Result values (1001,'ABC','2021-02-04',13)
insert into #Result values (1001,'DEF','2021-01-01',1)
insert into #Result values (1001,'DEF','2021-01-23',22)
select * from #Result
drop table #Result

I don't want to use cursor because the data is huge.

Thanks In Advance

Answer 1

Check a script that uses a loop and a series of joins:

create table #Result (ID int, GName nvarchar(100), GDate date, DayDiff int)

insert #Result(ID, GName, GDate, DayDiff)
select t1.ID, t1.GName, t1.GDate, 1
from #MyTable t1 
left join #MyTable t2 on t2.ID = t1.ID and t2.GName = t1.GName and t2.GDate < t1.GDate
where t2.ID is null

declare @f int = 1

while @f <> 0
begin

    insert #Result(ID, GName, GDate, DayDiff)
    select t1.ID, t1.GName, t1.GDate, datediff(day, r1.GDate, t1.GDate)
    from #MyTable t1 
    inner join #Result r1 on r1.ID = t1.ID and r1.GName = t1.GName and datediff(day, r1.GDate, t1.GDate) >= 10
    left join #MyTable t2 on t2.ID = t1.ID and t2.GName = t1.GName and datediff(day, r1.GDate, t2.GDate) >= 10 and t2.GDate < t1.GDate
    left join #Result r2 on r2.ID = r1.ID and r2.GName = r1.GName and r2.GDate > r1.GDate
    where t2.ID is null
    and r2.ID is null

    set @f = @@ROWCOUNT
end

select * 
from #Result
order by ID, GName, GDate

Answer 2

Please also try:

    DECLARE @i INT  
    DECLARE @ID INT  
      
    SET @i=1  
    SET @ID=1001  
    WHILE @i<(SELECT MAX(rr) FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ID,Gname ORDER BY ID) rr   
    FROM #MyTable) t)  
      
    BEGIN    
       IF (SELECT DATEDIFF(d,(SELECT GDate  
       FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ID,Gname ORDER BY ID) rr   
             FROM #MyTable) t  
       WHERE rr=@i+1 AND ID=@ID),(SELECT GDate  
       FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ID,Gname ORDER BY ID) rr   
             FROM #MyTable) t  
       WHERE rr=@i AND ID=@ID)))<10  
         
      
       DELETE    
       FROM #MyTable  
       WHERE GDate=  
       (SELECT GDate  
       FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ID,Gname ORDER BY ID) rr   
             FROM #MyTable) t  
       WHERE rr=@i+1)  
       AND ID=@ID  
      
       ELSE   
       SET @i=@i+1  
      
    END  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.