Re: Debit / Credit SQL Output
Hi,
I would try:
-- Select and sum all the credits
select acctnum,
sum(value) Credit,
0 Debit
from ar
where trantype = "Credit"
group by 1, 3
union
-- Select and sum all the debits
select acctnum,
0 Credit,
sum(value) Debit
from ar
where trantype = "Debit"
group by 1, 2
into temp AR;
-- Put the credits and debits together
select acctnum, sum(credit), sum(debit)
from AR
group by acctnum;
I have not run this so the syntax may not be exact but something like
this should work.
Regards - Lester
> 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
>
> Thanks a lot, Dan.
#############################################################################
# Lester Knutsen lester@advancedatatools.com #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
# Visit our Web page: www.advancedatatools.com #
# Washington Area Informix User Group: www.iiug.org/~waiug/ #
#############################################################################