Query the first and last record for a specific ID from a table

Based on your original content I have created the following Table and populated it. Please note that the variances in column names: this is because both Status and Location are special/reserved words in SQL Server and me editor (SSMS) highlighted them

CREATE TABLE TestData (
  PatientID   int,
  ApptDate    date,
  ApptStatus  varchar(32),
  VisitType   varchar(32),
  VisitTime   time,
  Facility    varchar(32)
)
GO
INSERT TestData VALUES
  (101, '01/01/2001', 'Complete', 'Flu complaint', '08:00', 'TeleMed')
, (101, '01/11/2001', 'Complete', 'Blood draw', '11:30', 'Outpatient Lab')
, (500, '05/01/2005', 'Complete', 'Broken Leg', '08:00', 'ER')
, (500, '06/01/2005', 'Complete', 'XRays', '17:00', 'Radiology')
, (600, '06/06/2005', 'Complete', 'Medical Complaint', '09:00', 'ER')
GO

The way I am going to write this is going to involve the use of 3 variables within the SQL environment. The first one (@PatientID) will be used to determine what patient you want the data on; and the other two (@FirstDate, @LastDate) will be used to determine the first and last appointment dates.

DECLARE @PatientID INT = 500
DECLARE @FirstDate DATE
DECLARE @LastDate  DATE

SELECT @FirstDate = Min(ApptDate), @LastDate = Max(ApptDate)
FROM   TestData
WHERE  (PatientID = @PatientID)

Now that we have these values all determined, we do not need to use sub-queries and it just becomes a matter of using these variables within a JOIN statement to flatten out the results into one row

SELECT f.PatientID
,      FirstApptDate   = f.ApptDate
,      FirstApptStatus = f.ApptStatus
,      FirstApptType   = f.VisitType
,      LastApptDate    = l.ApptDate
,      LastApptStatus  = l.ApptStatus
,      LastApptType    = l.VisitType
FROM       TestData f
INNER JOIN TestData l   ON f.PatientID = l.PatientID
                       AND f.ApptDate  = @FirstDate
                       AND l.ApptDate  = @LastDate
WHERE  (f.PatientID = @PatientID)

The solution you seek is going to involve a little more than a simple 1 line SQL statement, and the best way would utilize the Min and Max Aggregate functions
MIN (Transact-SQL) - SQL Server | Microsoft Docs[^]
MAX (Transact-SQL) - SQL Server | Microsoft Docs[^]

Once you work out how to get them values...
1. You get to use them within a subquery to get just the first appt info for that particular patient
2. You get to write another query/subquery combination to get the last appt info

OK, so you now have 2 queries (w/ subqueries) to get the first and last appt information.
Now you can JOIN them together to get the information all in one row.

This sample just has PatientID and the dates in it... It is not something I would use in production but it is fully functional. What it does show is how all of this can be done

DECLARE @Temp Table ( PatientID int, ApptDate date )

INSERT @Temp VALUES
    (101, '01/01/2001'), (101, '01/11/2001'), (500, '05/01/2005'), (500, '06/01/2005'), (600, '06/06/2005')

SELECT f.PatientID
,      FirstApptDate       = f.ApptDate
,      LastApptDate        = l.ApptDate

FROM (
     SELECT PatientID, ApptDate
     FROM   @Temp
     WHERE  ApptDate = (
          SELECT Min(ApptDate)
		FROM   @Temp
		WHERE  PatientID = 500 )         ) as F
INNER JOIN  (
     SELECT PatientID, ApptDate
     FROM   @Temp
     WHERE  ApptDate = (
	     SELECT FirstDate = Max(ApptDate)
          FROM   @Temp
          WHERE PatientID = 500)           ) as L ON  f.PatientID = l.PatientID
WHERE f.PatientID = 500

For a production script... this would be replaced with a Stored Procedure which would use a few more variables and not rely on all the sub-queries

select * from (select top 1 PatientID, APPTDATE, STATUS, VISITTYPE from ModifiedApptTable Where PatientID = 500 order by APPTDATE) a union select * from (select top 1 PatientID, APPTDATE, STATUS, VISITTYPE from ModifiedApptTableWhere PatientID = 500 order by APPTDATE desc) b

DROP TABLE #TMP 
SELECT [PatientID],
MAX(APPTDATE) M,MIN(APPTDATE) AS MN INTO #TMP
FROM TESTDATA S 
GROUP BY [PatientID];

WITH CTE
AS
(
SELECT T.[PatientID], [ApptDate], [ApptStatus], [VisitType], [VisitTime], [Facility],'MAXdATE'  AS RANGE
FROM TESTDATA T WHERE EXISTS (
SELECT  * FROM #TMP P WHERE P.[PatientID]=T.[PatientID] AND P.M=T.[ApptDate])
UNION ALL
SELECT T.[PatientID], [ApptDate], [ApptStatus], [VisitType], [VisitTime], [Facility],'MINDATE' AS RANGE
FROM TESTDATA T WHERE EXISTS (
SELECT  * FROM #TMP P WHERE P.[PatientID]=T.[PatientID] AND P.MN=T.[ApptDate])
)

SELECT * FROM CTE  WHERE [PatientID]=101