|Subject||RE: [firebird-support] Query Problem|
> I have one table BILLINGITEM, with individual charge lines.is your BILLINGITEMDATE field a date? or datetime?
> For each entry in BILLINGITEM, the PAYMENT table may have 0, 1 or more
> 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.AMOUNT * BI.UNITS CHARGES,
> SUM(PM.AMOUNT) CREDITS
> BILLINGITEM BI
> LEFT OUTER JOIN PAYMENT PM ON (BI.BILLINGITEMID = PM.BILLINGITEMID)
> BI.BILLINGITEMDATE <= :ASOFDATE AND
> (PM.PAYMENTDATE <= :ASOFDATE OR
> PM.PAYMENTDATE IS NULL)
> GROUP BY
> ORDER BY
> 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
> Again, if there are no payments before the as of date, but some
> after, then
> that record is not returned.
> *sobs* :(
> Please help!