Hello, 

I have a database that I am using to hold patient information. One
table has basic patient information, last name, first name, social
security, etc. The second table has appointment information,
appointment date, if the appointment was kept, etc. I have been trying
to figure out how to query the database to return the patient's last
name, first name, last kept appointment and next scheduled
appointment. I have been able to do this using seperate queries, but I
would like to do this with a single query.

Here are my sample tables.

patients
--------

pt_id  pt_first_name pt_last_name   pt_ss
------ ------------- -------------- -----------
1      james         pyle           000-00-0000
2      john          doe            111-11-1111
3      jane          doe            222-22-2222

appointments
-------------

appt_id  pt_id   appt_date   appt_status
-------- ----- ------------- ------------
1          1    06-23-2004       'K'
2          1    10-30-2004       'P'
3          2    04-22-2004       'M'
4          2    04-24-2004       'M'
5          2    05-01-2004       'K'
6          2    06-30-2004       'K'
7          2    08-26-2004       'P'
8          3    05-24-2004       'M'
9          3    05-25-2004       'K'
10         3    06-21-2004       'M' 

Note: 'M' - Missed, 'K' - Kept, 'P' - Pending

What I am trying to do it return the following: (Using today's date of 7/5/2004)

patient name last vist next visit
--------------------------------------------
Pyle, James  6-23-2004 10-30-2004
Doe, John    6-30-2004  8-26-2004
Doe, Jane    5-25-2004         --

Thanks for your help,

James

Hello James

I believe this is the SQL statement that you require:

SELECT patients.pt_first_name, patients.pt_last_name,
appointments.appt_date, appointments.appt_status
FROM patients
INNER  JOIN appointments ON patients.pt_id = appointments.pt_id
WHERE ( appointments.appt_status =  'K' OR appointments.appt_status =  'P' )
ORDER BY appointments.appt_date DESC

This will produce a table that includes all the information you
require and a little bit more.  You will need to then sort through the
data to pick out the information you require from the results
returned.  As you have not specified the language you are using I
cannot give you precise details here but I will set out below roughly
what I mean by this.

1) Loop through all the results and place them into an array (ArrayA).
2) Loop through ArrayA and pick out the latest dates for appointment
types K and P for each person and store this information into ArrayB.
3) Print out ArrayB.

I hope this answers your question as it was a tricky one to solve with
one SQL statement (and does require a little extra work by your script
after the records have been returned but does only call the database
once).  If you have any questions or queries on this please ask for
clarification and I will do my best to help.

---

Request for Answer Clarification by lutzguy-ga on 07 Jul 2004 06:49 PDT

Hello, Thanks for the reply.  I am actually using Crystal Reports
4.6.1 (I know it's an old version, but it works, and was free. :)  I
sorry for not puting that in my question in the first place.

Your query returns every patient along with every appointment they had
that is 'pending' or 'kept'.  I currently have 1,500 patients and
25,000 appointments in the database.  So it returns to much
information.

James

---

Clarification of Answer by palitoy-ga on 07 Jul 2004 07:14 PDT

Hello James

As I indicated in my answer you will need to further filter the
results using an array and a little programming to only display the
reduced data set.  To the best of my knowledge it is impossible to
pick only the last K and only the last P appointments for each
different patient with only one SQL statement without filtering the
information you receive from the query.  This filtering option would
be very easy to achieve in a language such as PHP but I am unfamiliar
with your Crystal Reports database software.

There are too many qualifications on the tables being queried for the
SQL statement to be done in one go.  You would normally use the
DISTINCT command to ensure that the results did not include duplicates
but in this case there are no duplicates due to the design of the
database.  It is possible you could create a temporary table and then
filter these results with additional SQL statements but this is
outside the scope of the original question as it requires more than
one SQL statement.

---

Clarification of Answer by palitoy-ga on 07 Jul 2004 09:34 PDT

Further to crythias-ga's comment, this is a similar solution to the
one I have proposed but you would need to do multiple database queries
to solve the task.  As you need to query with the SQL for each person
and as you have 1500+ patients this might not be feasible. 
crythias-ga's comments seem to re-affirm my belief what you need to do
is not achievable without a little extra programming (or by employing
some sort of macro).

Thanks for the information.  It have been very helpful.
James

Have you tried OR-ing your WHERE queries?

Last kept appointment is easy if you include LIMIT 1 in query and
ORDER by date DESC...

If we can hope that there is only one future appointment P and that
there is one K before the P, then the above query will work if you
LIMIT 2:

SELECT patients.pt_first_name, patients.pt_last_name,
appointments.appt_date, appointments.appt_status
FROM patients
INNER  JOIN appointments ON patients.pt_id = appointments.pt_id
WHERE ((appointments.pt_id=MyPatientIWantToLookUp) AND (
appointments.appt_status =  'K' OR appointments.appt_status =  'P' ))
ORDER BY appointments.appt_date DESC LIMIT 2

This WILL break if there are two or more P's for a patient. If that's
the case, you have no choice but to use two queries, unless you can
MIN the date of the 'P' somehow.

What about left joining patients with two instances of appointment,
and grouping by pt_id, something along the lines of:

SELECT p.pt_id, Max(a.appt_date) AS lastkept, Min(a2.appt_date) AS nextsched
FROM
(
patients AS p
LEFT JOIN
appointments AS a 
ON p.pt_id=a.pt_id AND a.appt_status='K'
)
LEFT JOIN
appointments AS a2
ON p.pt_id = a2.pt_id AND a2.appt_status = 'P'
GROUP BY p.pt_id;

Thanks to crythias-ga and lukatiks-ga also. I really appreciate your
comments and insight.  lukatiks-ga: Your query works!!!  It takes
about 7 minutes to execute, but it does work!

James