Debit / Credit SQL Output
Dan Armstrong
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.
Paul Roberts
> 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)
Mark D. Stock
Dan Armstrong wrote:
>
> I can't believe I am hung up on this one, but I just can't figure it
> out:
>
> 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.
>
>
> 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?
Have a look at the WHERE clause. Something like:
AFTER GROUP OF acctnum
PRINT COLUMN x, acctnum,
COLUMN y, GROUP SUM(value) WHERE trantype = "Credit",
COLUMN z, GROUP SUM(value) WHERE trantype = "Debit"
I haven't checked TFM, but I'll leave that to you. :-)
Hope that helps,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mds...@informix.com FAQ http://www.iiug.org |///// / //|
| +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+
Mark Fisher
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.
>
--
jo...@rl.is
Dan, I see two solutions to your puzzle (besides using temporary tables).
I) Create two simple stored procedures, e.g. called deb and crd. They
both accept an amount and a debit/credit flag (transaction type). The deb
procedure returns the value if it is a debit type, otherwise it returns
zero (or null). Similarly, crd returns only credit values. I leave it to
you to create the procedures but your select statement would ultimately
look something like:
select acctnum,sum(deb(value,trantype)),sum(crd(value,trantype))
from transactions group by acctnum
II) The following select statement should work all by itself:
select distinct acctnum,
(select sum(value) from transactions
where acctnum=tr.acctnum and trantype="Debit"),
(select sum(value) from transactions
where acctnum=tr.acctnum and trantype="Credit")
from transactions tr
The first solution is probably better in the long run as you run into the
problem over and over again. It also enables you to report ungrouped
detail data in debit/credit columns, like:
select acctnum,deb(value,trantype),crd(value,trantype) from transactions
Hope this helps,
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
fra...@centrum.is http://www.rl.is/~john/pow4gl.html
In article <33D25A3E...@stinky.quikrun.com>,
Dan Armstrong <da...@stinky.quikrun.com> wrote:
>
> 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.
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Kahler, Russell P.
This is an interesting problem. I would try looking at using a union
statement for your select statement.
select acctnum,value,0.00 from trans_table where transtype = "CREDIT"
union
select acctnum,0.00,value from trans_table where transtype = "DEBIT"
I don't know what type of reporting tool you are using to write your
report but whatever you are using should allow you to group by field1
and sum by fields 2 and 3.
this should provide you the results you are looking for. I have done
this type of reports before and found this to be the easiest way to do
it.....
hope this helps....
Russ...
Sujit Pal
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:da...@stinky.quikrun.com]
Sent: Sunday, July 20, 1997 12:34 PM
il...@technium.com
This may not be the easiest solution, but it seems to work...
SELECT acctnum, SUM(value) cr, 0 deb
FROM trantable
WHERE trantype = "Credit"
GROUP BY 1
UNION ALL
SELECT acctnum, 0 cr, SUM(value) deb
FROM trantable
WHERE trantype = "Debit"
GROUP BY 1
INTO TEMP junk WITH NO LOG;
SELECT acctnum, SUM(cr), SUM(deb)
FROM junk
GROUP BY 1;
You may want to drop "junk" after you're done.
Regards,
Eliott.
Paul Roberts
In article <5ril4l$o...@cssun.mathcs.emory.edu>,
Sujit Pal <sp...@scotch.den.csci.csc.com> wrote:
>
>Try as I might I could not do this with a single query. Maybe it can be
Someone posted a single-query solution a last week, it has expired at my
site but I think it went something like 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 unique t1Z.ACCTNUM, ( select sum(VALUE)
from t1 t1A
where t1A.ACCTNUM = t1Z.ACCTNUM
and t1A.TRANTYPE = 'Credit'
),
( select sum(VALUE)
from t1 t1B
where t1B.ACCTNUM = t1Z.ACCTNUM
and t1b.TRANTYPE = 'Debit'
)
from t1 t1Z;
which seems to work. Initially I didn't think it could be done without
temp tables, but I now stand corrected.
OK, here's my 4GL reporting puzzle: how do I produce a report ordered
like this:
ALABAMA LOUISIANA OHIO
ALASKA MAINE OKLAHOMA
ARIZONA MARYLAND OREGON
ARKANSAS MASSACHUSETTS PENNSYLVANIA
CALIFORNIA MICHIGAN RHODE ISLAND
COLORADO MINNESOTA SOUTH CAROLINA
CONNECTICUT MISSISSIPPI SOUTH DAKOTA
DELAWARE MISSOURI TENNESSEE
FLORIDA MONTANA TEXAS
GEORGIA NEBRASKA UTAH
HAWAII NEVADA VERMONT
IDAHO NEW HAMPSHIRE VIRGINIA
ILLINOIS NEW JERSEY WASHINGTON
INDIANA NEW MEXICO WEST VIRGINIA
IOWA NEW YORK WISCONSIN
KANSAS N CAROLINA WYOMING
KENTUCKY NORTH DAKOTA
One way would be load up an array, but that would set a limit on the number
of elements we would handle (since the array has to be declared as of being
of some finite size). Is there a better way to do it?
- Paul
Lester Knutsen
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 les...@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/ #
#############################################################################
Jonathan Leffler
Sujit Pal <sp...@scotch.den.csci.csc.com> wrote:
}Try as I might I could not do this with a single query. Maybe it can be done?
It can be done with a 'single query', but you don't need to look in the
manuals for an explanation of the syntax -- it isn't there. This sort of
thing has been possible, as far as I recall, since the 5.0x servers were
released. However, since it isn't documented, you might quite legitimately
decide not to use it.
With the exception of the fact that SQL returns NULL rather than 0 as the
sum of an empty set (see C J Date for more rantings on that subject; I
agree with him almost completely, FWIW), this query does what you want,
almost directly (I used a table accts for the data, rather than the table
AR used by Sujit):
SELECT
acctnum,
(SELECT SUM(value)
FROM accts A2
WHERE A1.acctnum = A2.Acctnum
AND A2.trantype = 'C'
GROUP BY acctnum
) AS credits,
(SELECT SUM(value)
FROM accts A3
WHERE A1.acctnum = A3.Acctnum
AND A3.trantype = 'D'
GROUP BY acctnum
) AS debits
FROM accts A1
GROUP BY acctnum;
acctnum credits debits
12000 100.00
13000 200.00 10.00
I made no mention of the term efficiency -- I think the versions using
UNION are more likely to be efficient on normal tables -- but I've not
looked at the query plans and this may work OK.
Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>
PS: Warning I do not reply to messages with anti-spam in the return path.
}From: Sujit Pal <sp...@scotch.den.csci.csc.com>
}Date: Mon, 28 Jul 1997 11:16:29 -0600
}X-Informix-List-Id: <list.15652>
}
}[...] 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 = "C"
}UNION ALL
}SELECT acctnum, 0 credit, value debit
} FROM ar
} WHERE trantype = "D"
}INTO TEMP t_ar;
}SELECT acctnum, sum(credit) credit, sum(debit) debit
} FROM t_ar
} GROUP BY 1
} ORDER BY 1;
[...]
}From: Dan Armstrong[SMTP:da...@stinky.quikrun.com]
}Sent: Sunday, July 20, 1997 12:34 PM
}
}I am writing an A/R accounting system [with a] Transactions table which
}basically contains an account number, a value (money) and whether it is a
}debit or credit.
}
}[...] 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
}13000 $100.00 Credit
}13000 $100.00 Credit
}13000 $10.00 Debit
[...]
Richard Stanford
On 28 Jul 1997, Paul Roberts wrote:
> OK, here's my 4GL reporting puzzle: how do I produce a report ordered
> like this:
>
> ALABAMA LOUISIANA OHIO
> ALASKA MAINE OKLAHOMA
> ARIZONA MARYLAND OREGON
[snip]
> One way would be load up an array, but that would set a limit on the number
> of elements we would handle (since the array has to be declared as of being
> of some finite size). Is there a better way to do it?
Absolutely. Throw the ordered data into a temp table with a serial key as
well, then pick your midpoints (being (number of values) / (number of
columns)) and do your selects "horizontally" by serial key. This has
worked well for us in the past.
-Richard