Re: [SQL] Debet-Credit-Balance Calculation

[Ramakrishnan Muralidharan]

Hi,      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:[EMAIL PROTECTED] Sent: Tuesday, April 19, 2005 10:18 PM To: pgsql-sql@postgresql.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