I have a table with names and visits data as follows

For each name on here, I want the results to roll up visitlicensedates into one (the first) if the visits are within 60 days of each other and use the last Expired date as the new licenseexpiredate. If the visit license was issued more than 60 days after the last, I want that to begin a new record so the results will be as follows:

I couldn't figure out the solution for this.

CodePudding user response：

You can use this stored procedure to get your expected results:

create procedure my_custom_sp
as

create table #tbl (
    [name] varchar(20),
    [visitlicensedate] date,
    [licenseExpiredate] date
)

declare cur cursor for 

select [name], [visitlicensedate] from tbl

declare @name varchar(20), @visitlicensedate date

open cur
fetch next from cur into @name, @visitlicensedate

while @@FETCH_STATUS =0 
begin   
    
    insert into #tbl ([name], [visitlicensedate], [licenseExpiredate])
    select [name], min([visitlicensedate]), max([licenseExpiredate])    
    from tbl t1 where [name] = @name 
    and [visitlicensedate] < DATEADD(d,60,@visitlicensedate)
    and not exists (
        select * from #tbl t2 where [name] = @name 
        and DATEDIFF(d,t2.[visitlicensedate],t1.[visitlicensedate]) < 60 
    )
    group by [name]
            
    fetch next from cur into @name, @visitlicensedate
end
close cur
deallocate cur

select * from #tbl

go

Result

exec my_custom_sp

/*
name    visitlicensedate    licenseExpiredate
John    2020-01-01          2020-05-02
John    2020-06-01          2020-09-30
James   2020-03-15          2020-06-14
*/