> 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. :(

is your BILLINGITEMDATE field a date? or datetime?
Alan

>
> 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