General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
>>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
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only