Find record which are greater or equal to 30 days from previous record

Question

I know we can use lag function to get the records who are greater than 30 days of the previous record.

I have a requirement attached in a image as shown in image

1st row is the start and I use that date to compare with the successive record, as soon as I find a record which is >=30 days, I need to take the date of that record and start comparing with successive records until I find a new record >= 30 days.

At the end after doing this I need records who are 30 days apart or null (first record)

Any way in SQL we can achieve this?

I tried using the lag function but that will compare with the previous record only , this will result in records which are >= 30days from the first record getting eliminated.

declare @Payment table 
(
    PersonId int
    , fromdate date
    , todate date
)
insert @Payment 
(PersonId, fromdate , todate )
values (1, '07/05/2014' , '07/06/2014' )
,(1, '07/15/2014' , '07/16/2014' )
,(1, '09/23/2014' , '09/24/2014')
,(1, '10/15/2014' , '10/16/2014')
,(1, '10/30/2014' , '10/31/2014')
,(2,  '07/20/2014' ,  '07/25/2014')
,(2, '08/20/2014' , '08/21/2014')
,(2, '09/21/2014' , '09/22/2014')

select * from @Payment

;with cte as
(select PersonId , fromdate , todate
       ,datediff(d
                ,  lag(todate) over (partition by PersonId order by fromdate)
                , fromdate)DaysSinceLastindex
 from @Payment )
select  PersonId , fromdate , todate , DaysSinceLastindex
from cte
where DaysSinceLastindex >= 30 or DaysSinceLastindex is null

Expected output is shown the image attached enter image description here

Answer 1

As I understand your question, we need a recursive query here:

with 
    data as (
        select p.*, row_number() over(partition by personid order by fromdate) rn
        from @payment p
    ),
    rcte as (
        select d.*, todate as refdate from data d where rn = 1
        union all
        select d.*, 
            case when datediff(d, r.refdate, d.fromdate) < 30 then r.refdate else d.todate end
        from rcte r
        inner join data d on d.personid = r.personid and d.rn = r.rn + 1
    )
select * from rcte where refdate = todate order by personid, fromdate

The first CTE enumerates the payments of each person. Then, the recursive query starts from the first payments, and processes rows in order, while keeping track of the "reference" start date and changing it when its gap with the current record exceeds 30 days. The last step is to just filter on the "first" record per group.

PersonId	fromdate	todate	rn	refdate
1	2014-07-05	2014-07-06	1	2014-07-06
1	2014-09-23	2014-09-24	3	2014-09-24
1	2014-10-30	2014-10-31	5	2014-10-31
2	2014-07-20	2014-07-25	1	2014-07-25
2	2014-09-21	2014-09-22	3	2014-09-22

fiddle

If a person may have more than 100 payments, then you need option (maxrecursion 0) at the end of your query.