How to roll up multiple rows into one if they meet a condition involving all the rows

Question

I have a table with names and visits data as follows

Name	Visitlicensedate	LicenseExpiredate
John	1/1/2020	3/30/2020
John	2/1/2020	5/2/2020
John	6/1/2020	9/30/2020
James	3/15/2020	6/14/2020

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:

Name	Visitlicensedate	LicenseExpiredate
John	1/1/2020	5/2/2020
John	6/1/2020	9/30/2020
James	3/15/2020	6/14/2020

I couldn't figure out the solution for this.

Answer 1

Alternatively, you can use the gaps and islands approach to put the data into groups and perform the calculation per group.

WITH table1 AS (
SELECT *,
   ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Visitlicensedate) AS rownum,
   LAG(Visitlicensedate) OVER (PARTITION BY Name ORDER BY Visitlicensedate) AS nextvisit,
   MIN(Visitlicensedate) OVER (PARTITION BY Name ORDER BY Name) AS sortdate
FROM mytable
),
table2 AS (
SELECT *,
   DATEDIFF(day, nextvisit, Visitlicensedate) AS gap
FROM table1
),
table3 AS (
SELECT *,
   SUM(CASE WHEN gap < 60 THEN 0 ELSE 1 END ) OVER (PARTITION BY Name ORDER BY rownum) AS grp
FROM table2
),
table4 AS (
SELECT Name, MIN(sortdate) AS sortdate, MIN(Visitlicensedate) AS Visitlicense_Date,
   MAX(LicenseExpiredate) AS LicenseExpire_Date
FROM table3
GROUP BY Name, grp
)
SELECT Name, Visitlicense_Date, LicenseExpire_Date
FROM table4
ORDER BY sortdate;

See Demo

Result

Name	Visitlicense_Date	LicenseExpire_Date
John	2020-01-01	2020-05-02
John	2020-06-01	2020-09-30
James	2020-03-15	2020-06-14