At 02:04 PM 27/08/2003 +0000, you wrote:

>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.
>
>Ive written the following, but the results aren't right. I was
>trying to join the table on itself.
>
>SELECT CO_NAME, SUM(f.SA_TOTAL), SUM(s.SA_TOTAL), SUM(t.SA_TOTAL)
>FROM SALESREG f
>LEFT JOIN SALESREG s ON (f.SA_CUSTID=s.SA_CUSTID)
>LEFT JOIN SALESREG t ON (s.SA_CUSTID=t.SA_CUSTID)
>LEFT JOIN COMPANY ON (f.SA_CUSTID=CO_IDNUM)
>WHERE (f.SA_AMTDUE > 0 OR s.SA_AMTDUE > 0 OR t.SA_AMTDUE > 0)
>AND(f.SA_INVDATE BETWEEN '8/21/2003' AND '8/27/2003')
>OR (s.SA_INVDATE BETWEEN '8/15/2003' AND '8/20/2003')
>OR (t.SA_INVDATE < '8/14/2003')
>GROUP BY CO_NAME
>
>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.

First thing is, Company has to be the leftmost table because it is
controlling the grouping and owns all the other joins. You presumably need
to get one row of output per company.

You have some potential ambiguities through unidentified columns - a few in
the joined query, and a lot in the subquery version.

Column names for the expression columns will come in handy. :-))

The logic of the WHERE clause is suspect. I'm assuming you want to AND the
two sets of ORs.

Try this:

SELECT c.CO_NAME,
SUM(f.SA_TOTAL) as ftotal,
SUM(s.SA_TOTAL) as stotal,
SUM(t.SA_TOTAL) as ttotal
FROM COMPANY C
LEFT JOIN SALESREG f ON f.SA_CUSTID=c.CO_IDNUM
LEFT JOIN SALESREG s ON s.SA_CUSTID=c.CO_IDNUM
LEFT JOIN SALESREG t ON t.SA_CUSTID=c.CO_IDNUM
WHERE (f.SA_AMTDUE > 0 OR s.SA_AMTDUE > 0 OR t.SA_AMTDUE > 0)
AND (
(f.SA_INVDATE BETWEEN '8/21/2003' AND '8/27/2003')
OR (s.SA_INVDATE BETWEEN '8/15/2003' AND '8/20/2003')
OR (t.SA_INVDATE < '8/14/2003')
)
GROUP BY c.CO_NAME

Not tested and it's late here, but you get the idea.

heLen