Hi Milan, thanks for your reply.

"I don't know how big are the tables, but index can make a HUGE difference."

Well at this site I converted tons of data from their old system (Pervasive
SQL database) into this one. That is one significant difference in this
installation, from my others, come to think of it.

OK, so perhaps my query would eventually return then, but just with a insane
time to run?

"Have you tried to sweep the problematic
database?"

From what I read in The Firebird Book, sweeps should occur automatically
right? I'll try to do it manually, thanks for the suggestion.

"BTW, I really don't see why you use OUTER joins? They can make
preformance really bad."

OK, the purpose of my query is to build a "day sheet" - basically I want to
report on any activity that occurs for a patient on a particular day. This
query basically gives me a listing for every patient that has any activity
on that day - either a charge, payment or refund.

As I build the report I then run other queries to get the actual specifics
for any of those activities (I gave up on trying to get it all in one
query).

So, if there is a charge but no payment or no refund associated then I will
not get that result back, so I believe I need an outer join here? Does this
make sense?

SELECT DISTINCT
DOCTOR.DOCTORID,
DOCTOR.LASTNAME,
DOCTOR.FIRSTNAME,
DOCTOR.MIDDLEINITIAL,
PATIENT.PATIENTID,
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
FROM
PATIENT
INNER JOIN BILLING ON (PATIENT.PATIENTID = BILLING.PATIENTID)
INNER JOIN BILLINGITEM ON (BILLING.BILLINGID = BILLINGITEM.BILLINGID)
LEFT OUTER JOIN PAYMENT ON (BILLING.BILLINGID = PAYMENT.BILLINGID)
LEFT OUTER JOIN REFUND ON (BILLING.BILLINGID = REFUND.BILLINGID)
INNER JOIN DOCTOR ON (BILLING.DOCTORID = DOCTOR.DOCTORID)
WHERE
BILLINGITEM.BILLINGITEMDATE = :BILLINGITEMDATE OR
PAYMENT.PAYMENTDATE = :BILLINGITEMDATE OR
REFUND.REFUNDDATE = :BILLINGITEMDATE
GROUP BY
DOCTOR.DOCTORID,
DOCTOR.LASTNAME,
DOCTOR.FIRSTNAME,
DOCTOR.MIDDLEINITIAL,
PATIENT.PATIENTID,
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
HAVING
SUM(PAYMENT.AMOUNT) <> 0 OR
SUM(BILLINGITEM.AMOUNT * BILLINGITEM.UNITS) <> 0 OR
SUM(REFUND.AMOUNT) <> 0
ORDER BY
DOCTOR.LASTNAME,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME