RE: Debit / Credit SQL Output
Dan
A thread similar to your query was running few days ago on this =
newsgroup. You might want to check out back issues. I have a solution =
for you using UNION and TEMP tables. Here it is:
CREATE TABLE ar ( /* Table definition */
acctnum INTEGER,
value DECIMAL(5,2),
trantype CHAR(1));
LOAD FROM ar.dat INSERT INTO ar; /* Load table w/your data */
SELECT acctnum, value credit, 0 debit /* Heres the query */
FROM ar
WHERE trantype =3D "C"
UNION ALL
SELECT acctnum, 0 credit, value debit
FROM ar
WHERE trantype =3D "D"
INTO TEMP t_ar;
SELECT acctnum, sum(credit) credit, sum(debit) debit
FROM t_ar
GROUP BY 1
ORDER BY 1;
Try as I might I could not do this with a single query. Maybe it can be =
done?
HTH
Sujit Pal
----------
From: Dan Armstrong[SMTP:dana@stinky.quikrun.com]
Sent: Sunday, July 20, 1997 12:34 PM
To: informix-list@rmy.emory.edu
Subject: Debit / Credit SQL Output
I can't believe I am hung up on this one, but I just can't figure it
out:
I am writing an A/R accounting system for a customer. I have an AR
Transactions table which basically contains an account number, a value
(money) and whether it is a debit or credit.
My problem is with the output. Any accounting report has the debits and
credits listed in two columns. What I need is to group by account
number, and put the sum(debits) and sum(credits) into two separate
columns. Like this:
ACCTNUM CREDITS DEBITS
12000 $100.00 $00.00
13000 $200.00 $10.00
etc.
The table I am trying to run a query against looks like this:
ACCTNUM VALUE TRANTYPE
12000 $50.00 Credit
12000 $50.00 Credit
13000 $100.00 Credit
13000 $100.00 Credit
13000 $10.00 Debit
That nasty Ansi compliant GROUP BY clause has got me hog tied here...I
have tried using the CASE statement, in which I get duplicate account
numbers in the output, I have even tried a self-join which yields
completely whacko results...This is so simple, does anybody have any
thoughts on the SQL Query that could yield the correct results?
Thanks a lot, Dan.