Hi,
I have to create a query that will look at patients with appointments. I need to see if these are new patients which is defined as any patient not seen in the past three years. The patient will have an appointment that will fall within a date range parameter so I would need to look at the previous appointment date to see if it was within three years of the current appointment date. The table has a patient ID, and ID identifying the appointment, and the appointment date. I was thinking of creating a temp table of all appointments and using it to compare to the list of appointments that fall within the date range parameter. I was wondering what would be the best way to do this?
Basic outline...
Start with a query that returns your list of patients - not knowing your tables lets call it Patient:
Select {columns}
From dbo.Patient p
Next - you want to get the latest appointment for each patient:
Select {columns}
From dbo.Patient p
Cross Apply (Select Top 1
a.AppointmentID
, a.StartDate
From dbo.Appointment a
Where a.PatientID = p.PatientID
And a.StartDate >= dateadd(year, -3, getdate())
Order By a.StartDate) As la
Now - you want to get the previous appointment and StartDate - if one exists:
Select {columns}
From dbo.Patient p
Cross Apply (Select Top 1
a.AppointmentID
, a.StartDate
From dbo.Appointment a
Where a.PatientID = p.PatientID
And a.StartDate >= {some date parameter}
And a.StartDate < {some date parameter}
Order By a.StartDate desc) As la
Outer Apply (Select Top 1
a2.AppointmentID
, a2.StartDate
From dbo.Appointment a2
Where a2.PatientID = p.PatientID
And a2.StartDate >= dateadd(year, -3, la.StartDate)
Order By a2.StartDate desc) As pa
Something like this should get you started...
1 Like
Thank you for your help!!