I'm trying to do something rather complicated (for me, anyway) and I can't seem to get it to work.
I have 3 tables. tmpbal A, pc B, pay C. Each of these has a client_num, client_src, and amount. I want to create a cursor that has client_num, client_src, and A.amount - SUM(B.amount) + SUM(C.amount) AS pbal.
Basically, tmpbal is the balance of each client/paysource combo as of the end of this billing period. I want to add to that the sum of all payments made during the billing period and subtract the sum of all trips charged in the billing period. The goal of this is to get the balance at the end of the _last_ billing period, ie: previous balance.
Any suggestions on how to do this?
I tried:
SELECT A.client_num, A.client_src, ;
A.amount - SUM(B.amount) + SUM(C.amount) AS pbal ;
FROM tempbal A ;
LEFT OUTER JOIN pc B ON...;
LEFT OUTER JOIN pay ON...;
etc...
But that doesn't give the right number. The answer should be 0, since none of them have previous balances, but I get $115 for one and null for the rest.
I appreciate any help you can give me...
-Michelle
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement