>>Hi
>>
>>I have finding a solution for this one:
>>
>>From a table containing customer numbers and total due i need to create a stored procedure or an sql statement creating the ageing of the total due.
>>
>>Our 3 Tables are
>>
>>Customers: Account, TotDue
>>Current transactions: Account, InvoiceNo, Date, Amount
>>Transaction History : Account, InvoiceNo, Date, Amount
>>
>>Starting with current transactions, then if neccessary, go to the history file. i need to get a cursor with invoice amounts in the appropriate columns, up to the total due.
>>
>>The cursor i need back would look like this:
>>
>>Account, Current, t30Day, t60Days, t90Days, t120Days
>>
>>
>>The problem is both the current transaction file and the history file have millions of records.
>>
>>We are using SQL Server 2000.
>>
>>Thanks for any help.
>
>You can do something like...
>
>SELECT
>Account
>,sum(CASE DATEDIFF(getdate(),DueDate)<=30 THEN Amount ELSE 0 END) AS Current
>,sum(CASE
> DATEDIFF(getdate(),DueDate)>30 AND DATEDIFF(getdate(),DueDate)<=60
> THEN Amount ELSE 0 END) AS t30Days
>,sum(CASE
> DATEDIFF(getdate(),DueDate)>60 AND DATEDIFF(getdate(),DueDate)<=90
> THEN Amount ELSE 0 END) AS t60Days
>,sum(etc...) AS t90Days
>,sum(etc...) AS t120Days
>WHERE
>Paid = 0
>GROUP BY Account

This does do the trick, i need to get the result from 2 tables and only up to the amount due Here is an example:

Customer table
---------------
AccountNo TotDue
1 500.00
2 125.00
... ...

Current transaction Table
-------------------------
Account InvoiceNo Date Amount
1 12345 2000/09/01 250.00
... .... ...... ....

Tansaction History Table
-------------------------
Account InvoiceNo Date Amount
1 45678 2000/08/15 125.00
1 55555 2000/07/15 125.00
1 55556 2000/05/15 255.00
2 55448 2000/01/15 500.00
... .... ...... ....


The resulting cursor i need for today

Account Current t30Day t60Days t90Days t120Days
1 250.00 125.00 125.00 0.00 0.00
2 0.00 0.00 0.00 0.00 125.00
...
...

Thanks For any help