Re: Debit / Credit SQL Output
> 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.
Well, how about this:
create temp table t1 ( ACCTNUM integer,
VALUE money(8,2),
TRANTYPE char(6) ) with no log ;
insert into t1 values ( 12000, 50.00 , "Credit" ) ;
insert into t1 values ( 12000, 50.00 , "Credit" ) ;
insert into t1 values ( 13000, 100.00 , "Credit" ) ;
insert into t1 values ( 13000, 100.00 , "Credit" ) ;
insert into t1 values ( 13000, 10.00 , "Debit" ) ;
select acctnum, sum(value) credits
from t1
where trantype = 'Credit'
group by 1
into temp t_cr with no log ;
select acctnum, sum(value) debits
from t1
where trantype = 'Debit'
group by 1
into temp t_db with no log ;
select unique acctnum
from t1
into temp allaccts with no log ;
select allaccts.acctnum, t_cr.credits, t_db.debits
from allaccts, outer t_cr, outer t_db
where allaccts.acctnum = t_cr.acctnum
and allaccts.acctnum = t_db.acctnum
order by 1 ;
which returns:-
acctnum credits debits
12000 $100.00
13000 $200.00 $10.00
Note that you get a NULL and not a zero for "12000's debits". In the report
you could do something along the lines of "if value is null print 0, else
print value end if".
- Paul (who knows he should get a life, but enjoys SQL puzzles)