?
Solved

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

Posted on 2005-04-29
6
Medium Priority
?
301 Views
Last Modified: 2010-03-19
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
Comment
Question by:theswally
  • 4
  • 2
6 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897390
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897398
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
 

Author Comment

by:theswally
ID: 13897421
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13897515
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
 

Author Comment

by:theswally
ID: 13897777
You are the Man!!!
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897782
Glad to be able to help.  Have a good day.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question