I have one table BILLINGITEM, with individual charge lines.

For each entry in BILLINGITEM, the PAYMENT table may have 0, 1 or more
entries.

I want to query the database and for every line in BILLINGITEM, I want to
get the charge and then the sum of any payments in the payment table.
Further I want to get this information, as of a certain date.

It seems like a simple outer join issue, however this query is kicking my
rear... :p

Here is the current version of my query:

SELECT DISTINCT
BI.BILLINGITEMID,
BI.AMOUNT * BI.UNITS CHARGES,
SUM(PM.AMOUNT) CREDITS
FROM
BILLINGITEM BI
LEFT OUTER JOIN PAYMENT PM ON (BI.BILLINGITEMID = PM.BILLINGITEMID)
WHERE
(
BI.BILLINGITEMDATE <= :ASOFDATE AND
(PM.PAYMENTDATE <= :ASOFDATE OR
PM.PAYMENTDATE IS NULL)
)
GROUP BY
BI.BILLINGITEMID,
BI.billingid,
BI.AMOUNT,
BI.UNITS
ORDER BY
BI.BILLINGITEMID

The problem is that if there are payments *after* the "ASOFDATE" but none
*before* then that record is not returned at all. :(

If there are no payments at all then the record is returned properly and if
there are any payments before the as of date then those are also returned
properly.

Again, if there are no payments before the as of date, but some after, then
that record is not returned.

*sobs* :(

Please help!

Thanks,

Anthony