Find if previous appointment date was within three years of current appointment date

Transact-SQL
1

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?

2

Please provide:

  • table definition as create statement
  • sample data as insert statement
  • the Query you are having problems with
  • expected output from the sample data you provide
3

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
4

Thank you for your help!!