Re: Debit / Credit SQL Output
Similar to this but with a SP and avoiding the outer joins
is a solution below. I'd be interested to see which would perform
better on large data. I always avoid outers like the plague, even on
temp tables. Add indexes as appropriate (ACC, TYPE)
Returns:
12000 $100.00 $0.00
13000 $200.00 $10.00
Mark Fisher
------------------------------------------
The views expressed here are mine and not
those of my employer, Britannic Assurance
------------------------------------------
-----------------8<-----------------------
create table t1 (
ACC int,
AMT money(8,2),
TYPE char(1) );
insert into t1 values ( 12000, 50.0, "C" );
insert into t1 values ( 12000, 50.0, "C" );
insert into t1 values ( 13000, 100.0, "C" );
insert into t1 values ( 13000, 100.0, "C" );
insert into t1 values ( 13000, 10.0, "D" );
create procedure spCD()
returning int, money(8,2), money(8,2);
define nC money(8,2);
define nD money(8,2);
define nAcc INT;
foreach
select distinct ACC
into nAcc
from t1
order by 1
select sum(AMT)
into nC
from t1
where TYPE = "C"
and ACC = nAcc;
select sum(AMT)
into nD
from t1
where TYPE = "D"
and ACC = nAcc;
if nC is NULL then
let nC = 0;
end if
if nD is NULL then
let nD = 0;
end if
return nAcc, nC, nD with resume;
end foreach
end procedure;
execute procedure spCD();
-----------------8<-----------------------
Paul Roberts wrote:
>
> > 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)
--