asked on

Tricky SQL Query, Getting the Most recent appointment for a customer

I have 2 tables, Invoice and Appointment, each containing the following records:

1. invoice(InvoiceID)
2. appointment (ID, InvoiceID, AppointmentDate, StartTime, EndTime, Status)

invoice
100
200
300

appointment
1,100,'4/20/05',9,11,'Completed'
2,200,'4/21/05',10,11,'Rescheduled'
3,200,'4/24/05',12,13,'Completed'
4,300,'4/22/05',8,11,'Cancelled'
5,300,'4/23/05',8,11,'Rescheduled'
6,300,'4/23/05',13,16,'Open'

I need to return the most recent appointment (max date and max start time) for each invoice.

The above 3 invoices would render the result:
(InvoiceID, Apptmt, StartTime, EndTime, Result)
100,'4/20/05',9,11,'Completed'
200,'4/24/05',12,13,'Completed'
300,'4/23/05',13,16,'Open'

This has me severely stumped. Thanks in advance.

rafrancisco

Try this:

SELECT A.InvoiceID, A.AppointmentDate, A.StartTime, A.EndTime, A.Status
FROM Appointment A
WHERE A.AppointmentDate = (SELECT MAX(B.AppointmentDate) FROM Appointment B
WHERE A.InvoiceID = B.InvoiceID)

rafrancisco

or this

SELECT A.InvoiceID, A.AppointmentDate, A.StartTime, A.EndTime, A.Status
FROM Appointment A INNER JOIN (SELECT InvoiceID, MAX(AppointmentDate) AS AppointmentDate from Appointment
GROUP BY InvoiceID) B
ON A.InvoiceID = B.InvoiceID AND
A.AppointmentDate = B.AppointmentDate

theswally

ASKER

That's a good start, but if there are 2 appointments on that same day, it might not be correct ( see Invoice 300 for an example of this, same date, one is at 8am, the other at 1pm.) I need to account for the start time as well.

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theswally

ASKER

You are the Man!!!

rafrancisco

Glad to be able to help. Have a good day.