• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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.
0
theswally
Asked:
theswally
  • 4
  • 2
1 Solution
 
rafranciscoCommented:
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)
0
 
rafranciscoCommented:
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
0
 
theswallyAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
rafranciscoCommented:
Ok, I didn't notice that.  How about this:

SELECT A.InvoiceID, A.AppointmentDate, A.StartTime, A.EndTime, A.Status
FROM Appointment A INNER JOIN
(SELECT InvoiceID, AppointmentDate, Max(StartTime) AS StartTime
 FROM Appointment X
 WHERE X.AppointmentDate = (SELECT MAX(AppointmentDate) AS AppointmentDate from Appointment Y
                                             WHERE X.InvoiceID = Y.InvoiceID)
 GROUP BY InvoiceID, AppointmentDate) B
ON A.InvoiceID = B.InvoiceID AND
     A.AppointmentDate = B.AppointmentDate AND
     A.StartTime = B.StartTime
0
 
theswallyAuthor Commented:
You are the Man!!!
0
 
rafranciscoCommented:
Glad to be able to help.  Have a good day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now