[sorry if this is almost a duplicate, I just discovered that I
originally sent the answer to ib-support rather than firebird-support as
well as noticed an error in my answer]

Hi Anthony!

I'll retract to your first SQL:

Anthony Tanas wrote:

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

Sure, that's what you asked for - either no payment at all (IS NULL) or
payment before the ASOFDATE. If you want rows returned from BI anyway,
then you cannot refer to PM in the WHERE CLAUSE. Change to

SELECT
BI.BILLINGITEMID,
BI.AMOUNT * BI.UNITS CHARGES,
SUM(PM.AMOUNT) CREDITS
FROM
BILLINGITEM BI
LEFT OUTER JOIN PAYMENT PM ON BI.BILLINGITEMID = PM.BILLINGITEMID
AND (PM.PAYMENTDATE <= :ASOFDATE1)
WHERE
BI.BILLINGITEMDATE <= :ASOFDATE2
GROUP BY
BI.BILLINGITEMID,
BI.AMOUNT,
BI.UNITS
HAVING BI.AMOUNT * BI.UNITS <> SUM(PM.AMOUNT)
ORDER BY
BI.BILLINGITEMID

and you should get what I think you want (well, I'm not certain about
the HAVING clause, I rarely use it. Maybe you have to add 'OR
SUM(PM.AMOUNT) IS NULL').

HTH,
Set