Subject | SQL aging report |
---|---|

Author | dixonepperson |

Post date | 2003-08-27T14:04:01Z |

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.

Dixon Epperson

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.

Dixon Epperson