Multi-Table Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multi-Table Query

Author

Topic

erikz
Starting Member

2 Posts

Posted - 2014-09-03 : 15:50:03

Consider the three tables:

Patients
Appointments
Invoices

Appointments and Invoices are related to Patients by the Patient ID.

I need a query to return all appointments on a date with patient detail (name, address, etc...) and the most recent invoice info (date, balance, due etc...) on or before the appointment date. If there is no invoice history then NULL invoice info is okay. for example this could be the patient's first appointment and would still need to be returned by the query. An appointment without a patient should be returned as well although this is contrary to the system business rules.

I am able to write the query to select the appointments and the patient info, but getting the last invoice for the patient on or before appointment date is something I am not sure how to do.

Thanks in advance,
Erik

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-03 : 18:55:50

You can use OUTER APPLY to get the most recent invoice data, if any; something like this:



SELECT a...., p...., i....
FROM Appointments a
INNER JOIN Patients p ON ...
OUTER APPLY (
    SELECT TOP (1) i2.*
    FROM Invoices i2
    WHERE i2.PatientID = a.PatientID AND i2.inv_date <= a.app_date
    ORDER BY i2.inv_date DESC
) AS i

erikz
Starting Member

2 Posts

Posted - 2014-09-05 : 08:34:43

Scott

Thanks for the reply. This seems like it should help. I'll have to do some testing. I'll post back if I need more help.

Thanks
Erik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources