SQL Server 2019

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

CREATE TABLE Appt1 (ApptID INT, PatientID INT, ApptDate DATE);
INSERT INTO Appt1
SELECT  1,101,'2020-01-05' UNION
SELECT  2,505,'2020-01-06' UNION
SELECT  3,505,'2020-01-10' UNION
SELECT  4,505,'2020-01-20' UNION
SELECT  5,101,'2020-01-25' UNION
SELECT  6,101,'2020-02-12'  UNION
SELECT  7,101,'2020-02-20'  UNION
SELECT  8,101,'2020-03-30'  UNION
SELECT  9,303,'2020-01-28' UNION
SELECT  10,303,'2020-02-02' 
10 rows affected

WITH f AS (
  SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY PatientId ORDER BY ApptDate) 
  FROM Appt1
), rec AS (
  SELECT Category = CAST('New' AS NVARCHAR(20)), ApptId, PatientId, ApptDate, rn, startDate = ApptDate
  FROM f
  WHERE rn = 1
  UNION ALL
  SELECT CAST(CASE WHEN DATEDIFF(DAY,  rec.startDate,f.ApptDate) <= 30 THEN N'FollowUp' ELSE N'New' END AS NVARCHAR(20)), 
         f.ApptId,f.PatientId,f.ApptDate, f.rn,
         CASE WHEN DATEDIFF(DAY, rec.startDate, f.ApptDate) <= 30 THEN rec.startDate ELSE f.ApptDate END
  FROM rec
  JOIN f
    ON rec.rn = f.rn - 1
   AND rec.PatientId = f.PatientId
)
SELECT ApptId, PatientId, ApptDate, Category
FROM rec
ORDER BY PatientId, ApptDate;


ApptId
PatientId
ApptDate
Category


1
101
2020-01-05
New

5
101
2020-01-25
FollowUp

6
101
2020-02-12
New

7
101
2020-02-20
FollowUp

8
101
2020-03-30
New

9
303
2020-01-28
New

10
303
2020-02-02
FollowUp

2
505
2020-01-06
New

3
505
2020-01-10
FollowUp

4
505
2020-01-20
FollowUp


CREATE TABLE Appt_temp (ApptID INT , PatientID INT, ApptDate DATE, Category NVARCHAR(10))

INSERT INTO Appt_temp(ApptId, PatientId, ApptDate)
SELECT ApptId, PatientId, ApptDate
FROM Appt1;

CREATE CLUSTERED INDEX Idx_appt ON Appt_temp(PatientID, ApptDate);
10 rows affected

DECLARE @PatientId INT = 0,
        @PrevPatientId INT,
        @FirstApptDate DATE = NULL;

UPDATE Appt_temp
SET  @PrevPatientId = @PatientId
    ,@PatientId     = PatientID 
    ,@FirstApptDate = CASE WHEN @PrevPatientId <> @PatientId THEN ApptDate
                           WHEN DATEDIFF(DAY, @FirstApptDate, ApptDate)>30 THEN ApptDate
                           ELSE @FirstApptDate
                      END
    ,Category       = CASE WHEN @PrevPatientId <> @PatientId THEN 'New'
                           WHEN @FirstApptDate = ApptDate THEN 'New'
                           ELSE 'FollowUp' 
                      END
FROM Appt_temp WITH(INDEX(Idx_appt))
OPTION (MAXDOP 1);
10 rows affected

SELECT * FROM  Appt_temp ORDER BY PatientId, ApptDate;


ApptID
PatientID
ApptDate
Category


1
101
2020-01-05
New

5
101
2020-01-25
FollowUp

6
101
2020-02-12
New

7
101
2020-02-20
FollowUp

8
101
2020-03-30
New

9
303
2020-01-28
New

10
303
2020-02-02
FollowUp

2
505
2020-01-06
New

3
505
2020-01-10
FollowUp

4
505
2020-01-20
FollowUp

ApptId	PatientId	ApptDate	Category
1	101	2020-01-05	New
5	101	2020-01-25	FollowUp
6	101	2020-02-12	New
7	101	2020-02-20	FollowUp
8	101	2020-03-30	New
9	303	2020-01-28	New
10	303	2020-02-02	FollowUp
2	505	2020-01-06	New
3	505	2020-01-10	FollowUp
4	505	2020-01-20	FollowUp