I do I determine the balance due using a Select Statment/

[rpp]

I have table that includes a column of CHARGES and also includes a column of PAYMENTS both with data for customer billing. Do I need 2 select statments then compare them together to determine if the account has a balance?

I am uncertain as to the best way SUM the each field and then compare to each other to determine if one as a balance.

Any help would be appreciated.

TIA!
RPP

 

[danceman]

what groups the charges and payments, I am assuming that a customer has more then one charge and payment. also is there an invoice to be concerned with? so thee may be groups of invoice then group by customer. can you provided some info as to the table structure or is more then one table involved, in a billing system there usually is. Attitude is Everything

 

[rpp]

So sorry, you are right I did not include this information, there are multiple records for a single customer, both credits and debits. Through some trial and error I have the following working:

SELECT xar.*, ;
SUM(CHARGES) AS sumcharges, ;
SUM(PAYMENT) AS sumpayment, ;
ROUND(SUM(charges)-SUM(payment),2) AS Balance ;
FROM xar ;
GROUP BY KEY ;
HAVING ROUND(SUM(charges),2) > ROUND(sum(PAYMENT),2) ;
INTO CURSOR TEST1 ;
ORDER BY sortname

This is the first time I have had to deal with "SUMS" I have read more from the manual, it helped somewhat.

 

[rgbean]

rpp,
Just so that you know, the only field in your xar.* list that you can be sure of is KEY. i.e. Only GROUP BY fields and aggregate function fields (like SUM, COUNT, MIN, MAX, etc.)are meanful. All other fields that vary from record to record within a KEY value, may be from the first, last or any other record in the group. Basically these values are officially undefined, and many SQL syntaxs will give you an error. I pass this along, just so you won't assume the data you get in any of these other fields is meaningful or consistent.

Rick

 

[danceman]

since I don't have a table to work with , this looks like you are heading on the right track.
SELECT xar.*, ;
SUM(CHARGES) AS sumcharges, ;
SUM(PAYMENT) AS sumpayment, ;
ROUND(sumcharges-sumpayment,2) AS Balance ;
FROM xar ;
GROUP BY KEY ;
HAVING ROUND(sumcharges,2) > ROUND(sumpayment,2) ;
OR HAVING sumcharges > sumpayment ;
INTO CURSOR TEST1 ;
ORDER BY sortname

there is no need to repeat yourself with the SUM, I see this a lot it causes extra work. behind the seans, there are working tables being created, and each operation causes extra work. also if you need to point to your self you have what is called self joins. In FAQ there is an example of it, but basicly you can treat the table as many as you want with calling it another name.
if you can only pick the fields you need in the select statement. Attitude is Everything