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)