Subject | Re: [firebird-support] SQL aging report |
---|---|

Author | Arno Brinkman |

Post date | 2003-08-27T14:27:33Z |

Hi,

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

> I am bouncing all around this and still not getting the rightTwo problems here, don't forget to alias your tables and due a bug in

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

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