CODEWITHSUNDEEP
sqlsql-server-2008
Michal
Michal

Reputation: 5828

Select last appointment for a given specialist and a referral leading to it

For the sake of simplicity, let’s assume that the table in question is called app and it has only three fields:

Person_id | employee_id | appointment_time
----------+-------------+-----------------
int       | int         | date

The table holds details of all medical appointments, past and future, for all clients (person_id) and specialists (employee_id).

What I am trying to figure out is how to create a list of appointments for a given specialist (let's say with an id of 235) and their corresponding "referals" (if any) - the previous appointment for a given person_id with an earlier date and serviced by another specialist (id <> 235).

SELECT
  qLast.person_id,
  qLast.employee_id,
  qLast.LastDate,
  qPrevious.employee_id,
  qPrevious.PreviousDate
FROM
  (
    SELECT
      app.person_id,
      app.employee_id,
      Max(app.appointment_time) AS LastDate
    FROM
      app
    GROUP BY
      app.person_id,
      app.employee_id
    HAVING
      app.person_id <> 0
      AND app.employee_id = 235
  ) qLast
  LEFT JOIN (
    SELECT
      qSub.person_id,
      app.employee_id,
      qSub.MaxOfappointment_time AS PreviousDate
    FROM
      (
        SELECT
          app.person_id,
          Max(app.appointment_time) AS MaxOfappointment_time
        FROM
          app
        GROUP BY
          app.person_id,
          app.employee_id
        HAVING
          app.person_id <> 0
          AND app.employee_id <> 235
      ) qSub
      INNER JOIN app ON (
        qSub.MaxOfappointment_time = app.appointment_time
      )
      AND (qSub.person_id = app.person_id)
  ) qPrevious ON qLast.person_id = qPrevious.person_id;

My mangled attempt almost works but sadly falls on its confused face when there is an appointment for a specialist with id<>235 with a later date than the last appointment for id=235. For now I run another query on the results of this one to filter out the unwanted records but it a rather ugly kludge. I'm sure there is a better and more elegant way of solving it. Help please!

Upvotes: 0

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

I think you basically want lag(), but that is not available in SQL Server 2008 (time to upgrade to supported software!).

You can use apply instead:

select a.*, a2.*
from app a cross apply
     (select top (1) a2.*
      from app a2
      where a2.person_id = a.person_id and
            a2.employee_id <> a.employee_id and
            a2.appointment_time < a.appointment_time
      order by a2.appointment_time desc
     ) a2

Upvotes: 1

Related Questions