SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
    Name VARCHAR(20),
    Visitlicensedate Date,
    LicenseExpiredate Date
    );
    
INSERT INTO mytable VALUES
('John', '2020-01-01', '2020-03-30'),
('John', '2020-02-01', '2020-05-02'),
('John', '2020-06-01', '2020-09-30'),
('James', '2020-03-15', '2020-06-14');
    
    
4 rows affected

SELECT * FROM mytable;


Name
Visitlicensedate
LicenseExpiredate


John
2020-01-01
2020-03-30

John
2020-02-01
2020-05-02

John
2020-06-01
2020-09-30

James
2020-03-15
2020-06-14


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;



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

Name	Visitlicensedate	LicenseExpiredate
John	2020-01-01	2020-03-30
John	2020-02-01	2020-05-02
John	2020-06-01	2020-09-30
James	2020-03-15	2020-06-14