Hi,

> I am bouncing all around this and still not getting the right
> answer. I need an sql statement that can generate an aging report,
> that is the sum of invoices which are less than 30 days old with
> balance outstanding, sum between 30 and 60 days and sum over 90.
>
> I also wrote the following subselect but again the results aren't
> right.
> SELECT CO_NAME,
> (SELECT SUM(SA_TOTAL) FROM SALESREG LEFT JOIN COMPANY ON
> (SA_CUSTID=CO_IDNUM)
> WHERE (SA_INVDATE BETWEEN '8/21/2003' AND '8/27/2003') AND
> SA_CUSTID=CO_IDNUM),
>
> (SELECT SUM(SA_TOTAL) FROM SALESREG LEFT JOIN COMPANY ON
> (SA_CUSTID=CO_IDNUM)
> WHERE (SA_INVDATE BETWEEN '8/15/2003' AND '8/20/2003') AND
> SA_CUSTID=CO_IDNUM),
>
> (SELECT SUM(SA_TOTAL) FROM SALESREG LEFT JOIN COMPANY ON
> (SA_CUSTID=CO_IDNUM)
> WHERE (SA_INVDATE BETWEEN '8/11/2003' AND '8/14/2003') AND
> SA_CUSTID=CO_IDNUM)
>
> FROM SALESREG
> LEFT JOIN COMPANY ON (SA_CUSTID=CO_IDNUM)
> GROUP BY CO_NAME
>
> I sure would appreciate some help with this. I don't quite
> understand what i am missing.

Two problems here, don't forget to alias your tables and due a bug in
Interbase X and FB1.0 you can use CO_IDNUM (from the base context) inside
the sub-query without putting it into the GROUP BY clause. When you change
the query to :

SELECT
c1.CO_NAME,
(SELECT SUM(s2.SA_TOTAL) FROM SALESREG s2
LEFT JOIN COMPANY c ON (s2.SA_CUSTID = c.CO_IDNUM)
WHERE (s2.SA_INVDATE BETWEEN '8/21/2003' AND '8/27/2003') AND
s2.SA_CUSTID = c1.CO_IDNUM),
(SELECT SUM(s2.SA_TOTAL) FROM SALESREG s2
LEFT JOIN COMPANY c ON (s2.SA_CUSTID = c.CO_IDNUM)
WHERE (s2.SA_INVDATE BETWEEN '8/15/2003' AND '8/20/2003') AND
s2.SA_CUSTID = c1.CO_IDNUM),
(SELECT SUM(s2.SA_TOTAL) FROM SALESREG
LEFT JOIN COMPANY c ON (s2.SA_CUSTID = c.CO_IDNUM)
WHERE (s2.SA_INVDATE BETWEEN '8/11/2003' AND '8/14/2003') AND
s2.SA_CUSTID = c1.CO_IDNUM)
FROM
SALESREG s1
LEFT JOIN COMPANY c1 ON (s1.SA_CUSTID = c1.CO_IDNUM)
GROUP BY
c1.CO_NAME,
c1.CO_IDNUM

Note! Maybe i forgot a alias somewhere !

Does it return the correct results now?

Regards,
Arno