Re: Debet-Credit-Balance Calculation

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Using a table with larger data in the sub-query always eats up CPU time and it is time consuming. The below given statement uses the transaction table two times in the sub-query and for processing every record, it have to go through the same table twice in the sub-query. It is better to write a function to process the data and return back the record set.

Regards,

R.Muralidharan

-----Original Message-----
From: Muhyiddin A.M Hayat [mailto:middink(at)indo(dot)net(dot)id]
Sent: Tuesday, April 19, 2005 10:18 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Debet-Credit-Balance Calculation

Dear All,

I have problem to calculation balance from debet and credit.

my transaction table:

id | trx_timestamptz | account | trx_type_id | amount
----+------------------------+------------------+-------------+-------------
3 | 2005-04-14 17:16:49+08 | 01.2010100.00002 | 1 | 1000000.00
4 | 2005-04-14 17:17:12+08 | 01.2010100.00002 | 1 | 50000.00
5 | 2005-04-14 17:17:12+08 | 01.2010100.00003 | 1 | 600000.00
6 | 2005-04-14 17:17:47+08 | 01.2010100.00002 | 2 | 7000.00
7 | 2005-04-16 00:32:50+08 | 01.2010100.00003 | 1 | 20000.00
11 | 2005-04-16 02:45:06+08 | 01.2010100.00002 | 1 | 100000.00
12 | 2005-04-16 02:46:02+08 | 01.2010100.00002 | 1 | 20000.00
13 | 2005-04-16 02:46:59+08 | 01.2010100.00002 | 2 | 163000.00
14 | 2005-04-16 02:50:17+08 | 01.2010100.00005 | 1 | 100000.00
15 | 2005-04-16 02:53:42+08 | 01.2010301.00001 | 1 | 100000.00
16 | 2005-04-16 02:57:22+08 | 01.2010100.00001 | 1 | 2000000.00
17 | 2005-04-16 23:56:44+08 | 01.2010200.00000 | 1 | 10000000.00
18 | 2005-04-17 18:58:57+08 | 01.2010100.00003 | 1 | 100000.00
19 | 2005-04-17 19:13:05+08 | 01.2010100.00002 | 1 | 100000.00
20 | 2005-04-17 19:13:45+08 | 01.2010100.00002 | 1 | 200000.00
21 | 2005-04-17 19:15:36+08 | 01.2010100.00002 | 1 | 50000.00
22 | 2005-04-17 19:17:17+08 | 01.2010100.00005 | 2 | 10000.00
23 | 2005-04-17 19:18:06+08 | 01.2010100.00004 | 1 | 200000.00
24 | 2005-04-17 21:45:31+08 | 01.2010100.00002 | 1 | 9000.00
25 | 2005-04-17 22:16:08+08 | 01.2010100.00006 | 1 | 100000.00

-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "public"."transactions" (
"id" SERIAL,
"trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
"account" CHAR(16) NOT NULL,
"trx_type_id" INTEGER NOT NULL,
"amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,
"uid" INTEGER NOT NULL,
CONSTRAINT "transactions_pkey" PRIMARY KEY("id")
) WITH OIDS;
-------------------------------------------------------------------------------------------

and transaction type :

id | trx_name | t_type
----+----------+--------
1 | Credit | CR
2 | Debet | DB

---------------------------------------------------------------------
CREATE TABLE "public"."trx_type" (
"id" SERIAL,
"trx_name" VARCHAR(32),
"t_type" CHAR(2),
CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),
CONSTRAINT "trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type = 'DB'::bpchar))
) WITH OIDS;

---------------------------------------------------------------------

so, i'm using this query:

SELECT
trans.id,
trans.trx_timestamptz,
trans.account,
trans.debet,
trans.credit
FROM
(
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,

CASE
WHEN trx_type.t_type = 'DB' THEN
transactions.amount
ELSE
0
END AS debet,
CASE
WHEN trx_type.t_type = 'CR' THEN
transactions.amount
ELSE
0
END AS credit
FROM
transactions
INNER JOIN trx_type ON (transactions.trx_type_id = trx_type.id)

) AS trans

result from above query :

id | trx_timestamptz | account | debet | credit
----+------------------------+------------------+-----------+-------------
3 | 2005-04-14 17:16:49+08 | 01.2010100.00002 | 0 | 1000000.00
4 | 2005-04-14 17:17:12+08 | 01.2010100.00002 | 0 | 50000.00
5 | 2005-04-14 17:17:12+08 | 01.2010100.00003 | 0 | 600000.00
6 | 2005-04-14 17:17:47+08 | 01.2010100.00002 | 7000.00 | 0
7 | 2005-04-16 00:32:50+08 | 01.2010100.00003 | 0 | 20000.00
11 | 2005-04-16 02:45:06+08 | 01.2010100.00002 | 0 | 100000.00
12 | 2005-04-16 02:46:02+08 | 01.2010100.00002 | 0 | 20000.00
13 | 2005-04-16 02:46:59+08 | 01.2010100.00002 | 163000.00 | 0
14 | 2005-04-16 02:50:17+08 | 01.2010100.00005 | 0 | 100000.00
15 | 2005-04-16 02:53:42+08 | 01.2010301.00001 | 0 | 100000.00
16 | 2005-04-16 02:57:22+08 | 01.2010100.00001 | 0 | 2000000.00
17 | 2005-04-16 23:56:44+08 | 01.2010200.00000 | 0 | 10000000.00
18 | 2005-04-17 18:58:57+08 | 01.2010100.00003 | 0 | 100000.00
19 | 2005-04-17 19:13:05+08 | 01.2010100.00002 | 0 | 100000.00
20 | 2005-04-17 19:13:45+08 | 01.2010100.00002 | 0 | 200000.00
21 | 2005-04-17 19:15:36+08 | 01.2010100.00002 | 0 | 50000.00
22 | 2005-04-17 19:17:17+08 | 01.2010100.00005 | 10000.00 | 0
23 | 2005-04-17 19:18:06+08 | 01.2010100.00004 | 0 | 200000.00
24 | 2005-04-17 21:45:31+08 | 01.2010100.00002 | 0 | 9000.00
25 | 2005-04-17 22:16:08+08 | 01.2010100.00006 | 0 | 100000.00


my problem, i would like to place balance in last field. i try using this query :

SELECT
trans.id,
trans.trx_timestamptz,
trans.account,
trans.debet,
trans.credit,

(SELECT
SUM(coalesce(credit,0)-coalesce(debet,0))
FROM
(
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,

CASE
WHEN trx_type.t_type = 'DB' THEN
transactions.amount
ELSE
0
END AS debet,
CASE
WHEN trx_type.t_type = 'CR' THEN
transactions.amount
ELSE
0
END AS credit
FROM
transactions
INNER JOIN trx_type ON (transactions.trx_type_id = trx_type.id)

) sub
WHERE
(sub.id <= trans.id)
AND
(sub.account = trans.account)
)
AS balance

FROM
(
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,

CASE
WHEN trx_type.t_type = 'DB' THEN
transactions.amount
ELSE
0
END AS debet,
CASE
WHEN trx_type.t_type = 'CR' THEN
transactions.amount
ELSE
0
END AS credit
FROM
transactions
INNER JOIN trx_type ON (transactions.trx_type_id = trx_type.id)

) AS trans

-----

everything is ok, but when record > 1000000 that query eat all my cpu process and take a long time, i have wait for 3 mimutes but query doesn't finish. (pgsql-8.0-1 running on Dual Xeon 2.8 and 2GB of RAM)

can you help me how to fix them.

Thanks