X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2614F52868 for ; Sun, 22 May 2005 01:17:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77593-07 for ; Sun, 22 May 2005 04:16:50 +0000 (GMT) Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.98]) by svr1.postgresql.org (Postfix) with SMTP id 3379952845 for ; Sun, 22 May 2005 01:16:45 -0300 (ADT) Received: (qmail 11592 invoked from network); 22 May 2005 11:17:20 +0700 Received: from smtp.indo.net.id (202.159.32.81) by smtp-1.indo.net.id with SMTP; 22 May 2005 11:17:20 +0700 Received: (qmail 20311 invoked from network); 22 May 2005 11:17:15 +0700 Received: from unknown (HELO middinks) (202.159.122.117) by smtp.indo.net.id with SMTP; 22 May 2005 11:17:15 +0700 Message-ID: <033001c55e85$1801a730$1f00a8c0@middinks> From: "Muhyiddin A.M Hayat" To: Subject: Balance Calculation Date: Sun, 22 May 2005 12:15:56 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_032D_01C55EC8.01C1B7B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.438 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_WHOIS, HTML_50_60, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200505/175 X-Sequence-Number: 21488 This is a multi-part message in MIME format. ------=_NextPart_000_032D_01C55EC8.01C1B7B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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,=20 "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT = ('now'::text)::timestamp(6) with time zone NOT NULL,=20 "account" CHAR(16) NOT NULL,=20 "trx_type_id" INTEGER NOT NULL,=20 "amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,=20 "uid" INTEGER NOT NULL,=20 CONSTRAINT "transactions_pkey" PRIMARY KEY("id"),=20 CONSTRAINT "transactions_trx_type_id_fkey" FOREIGN KEY ("trx_type_id") REFERENCES "public"."trx_type"("id") ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE ) WITH OIDS; -------------------------------------------------------------------------= ------------------ and transaction type : id | trx_name | t_type ----+----------+-------- 1 | Credit | CR 2 | Debet | DB --------------------------------------------------------------------- CREATE TABLE "public"."trx_type" ( "id" SERIAL,=20 "trx_name" VARCHAR(32),=20 "t_type" CHAR(2),=20 CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),=20 CONSTRAINT "trx_typei_check0" CHECK ((t_type =3D 'CR'::bpchar) OR = (t_type =3D 'DB'::bpchar)) ) WITH OIDS;=20 --------------------------------------------------------------------- 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 =3D 'DB' THEN transactions.amount ELSE 0 END AS debet, CASE WHEN trx_type.t_type =3D 'CR' THEN transactions.amount ELSE 0 END AS credit FROM transactions INNER JOIN trx_type ON (transactions.trx_type_id =3D 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 =3D 'DB' THEN transactions.amount ELSE 0 END AS debet, CASE WHEN trx_type.t_type =3D 'CR' THEN transactions.amount ELSE 0 END AS credit FROM transactions INNER JOIN trx_type ON (transactions.trx_type_id =3D trx_type.id) ) sub WHERE (sub.trx_timestamptz <=3D trans.trx_timestamptz) AND (sub.account =3D trans.account) ) AS balance FROM ( SELECT transactions.id, transactions.trx_timestamptz, transactions.account, CASE WHEN trx_type.t_type =3D 'DB' THEN transactions.amount ELSE 0 END AS debet, CASE WHEN trx_type.t_type =3D 'CR' THEN transactions.amount ELSE 0 END AS credit FROM transactions INNER JOIN trx_type ON (transactions.trx_type_id =3D 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. Can I using function to fix them (how?) Thanks ------=_NextPart_000_032D_01C55EC8.01C1B7B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Dear All,

I have problem to = calculation=20 balance from debet and credit.

my transaction table:
 
 id |   =20 trx_timestamptz     |    =20 account      | trx_type_id |  =20 amount
----+------------------------+------------------+-------------+= -------------
 =20 3 | 2005-04-14 17:16:49+08 | 01.2010100.00002=20 |           1 | =20 1000000.00
  4 | 2005-04-14 17:17:12+08 | 01.2010100.00002=20 |           1=20 |    50000.00
  5 | 2005-04-14 17:17:12+08 |=20 01.2010100.00003 = |           1=20 |   600000.00
  6 | 2005-04-14 17:17:47+08 | = 01.2010100.00002=20 |           2=20 |     7000.00
  7 | 2005-04-16 00:32:50+08 | = 01.2010100.00003 = |           1=20 |    20000.00
 11 | 2005-04-16 02:45:06+08 |=20 01.2010100.00002 = |           1=20 |   100000.00
 12 | 2005-04-16 02:46:02+08 | = 01.2010100.00002=20 |           1=20 |    20000.00
 13 | 2005-04-16 02:46:59+08 |=20 01.2010100.00002 = |           2=20 |   163000.00
 14 | 2005-04-16 02:50:17+08 | = 01.2010100.00005=20 |           1 = |  =20 100000.00
 15 | 2005-04-16 02:53:42+08 | 01.2010301.00001=20 |           1 = |  =20 100000.00
 16 | 2005-04-16 02:57:22+08 | 01.2010100.00001=20 |           1 | =20 2000000.00
 17 | 2005-04-16 23:56:44+08 | 01.2010200.00000=20 |           1 |=20 10000000.00
 18 | 2005-04-17 18:58:57+08 | 01.2010100.00003=20 |           1 = |  =20 100000.00
 19 | 2005-04-17 19:13:05+08 | 01.2010100.00002=20 |           1 = |  =20 100000.00
 20 | 2005-04-17 19:13:45+08 | 01.2010100.00002=20 |           1 = |  =20 200000.00
 21 | 2005-04-17 19:15:36+08 | 01.2010100.00002=20 |           1=20 |    50000.00
 22 | 2005-04-17 19:17:17+08 |=20 01.2010100.00005 = |           2=20 |    10000.00
 23 | 2005-04-17 19:18:06+08 |=20 01.2010100.00004 = |           1=20 |   200000.00
 24 | 2005-04-17 21:45:31+08 | = 01.2010100.00002=20 |           1=20 |     9000.00
 25 | 2005-04-17 22:16:08+08 | = 01.2010100.00006 = |           1=20 |   100000.00
 
----------------------------------------------------------------= ---------------------------------------------------------
CREATE TABLE "public"."transactions" = (
 =20 "id" SERIAL,
  "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE = DEFAULT=20 ('now'::text)::timestamp(6) with time zone NOT NULL,
  = "account"=20 CHAR(16) NOT NULL,
  "trx_type_id" INTEGER NOT NULL,
  = "amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,
  "uid" INTEGER NOT = NULL,=20
  CONSTRAINT "transactions_pkey" PRIMARY KEY("id"),
 =20 CONSTRAINT "transactions_trx_type_id_fkey" FOREIGN KEY=20 ("trx_type_id")
    REFERENCES=20 "public"."trx_type"("id")
    ON DELETE=20 CASCADE
    ON UPDATE CASCADE
    = NOT=20 DEFERRABLE
) WITH OIDS;
----------------------------------------------------------------= ---------------------------
 
and transaction type :
 
 id | trx_name |=20 t_type
----+----------+--------
  1 | Credit   |=20 CR
  2 | Debet    | DB
 
----------------------------------------------------------------= -----
CREATE TABLE "public"."trx_type" (
  "id" = SERIAL,=20
  "trx_name" VARCHAR(32),
  "t_type" CHAR(2), =
 =20 CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),
  CONSTRAINT=20 "trx_typei_check0" CHECK ((t_type =3D 'CR'::bpchar) OR (t_type =3D=20 'DB'::bpchar))
) WITH OIDS;=20
 
----------------------------------------------------------------= -----
 
so, i'm using this query:
 
SELECT
  trans.id,
 =20 trans.trx_timestamptz,
  trans.account,
  = trans.debet,
 =20 trans.credit
FROM
  (
   =20 SELECT
     =20 transactions.id,
     =20 transactions.trx_timestamptz,
     =20 transactions.account,
 
     =20 CASE
        WHEN = trx_type.t_type  =3D=20 'DB' THEN
         =20 transactions.amount
       =20 ELSE
         =20 0
      END AS=20 debet,
      = CASE
     =20 WHEN trx_type.t_type =3D 'CR'=20 THEN
         =20 transactions.amount
       =20 ELSE
         =20 0
      END AS credit
   =20 FROM
     =20 transactions
      INNER JOIN trx_type ON=20 (transactions.trx_type_id =3D trx_type.id)
 
  ) AS trans
 
result from above query :
 
 id |   =20 trx_timestamptz     |    =20 account      |   debet  =20 |  =20 credit
----+------------------------+------------------+-----------+--= -----------
 =20 3 | 2005-04-14 17:16:49+08 | 01.2010100.00002=20 |         0 |  = 1000000.00
 =20 4 | 2005-04-14 17:17:12+08 | 01.2010100.00002=20 |         0 |   =20 50000.00
  5 | 2005-04-14 17:17:12+08 | 01.2010100.00003=20 |         0 |  =20 600000.00
  6 | 2005-04-14 17:17:47+08 | 01.2010100.00002 = |  =20 7000.00 |          =20 0
  7 | 2005-04-16 00:32:50+08 | 01.2010100.00003=20 |         0 |   =20 20000.00
 11 | 2005-04-16 02:45:06+08 | 01.2010100.00002=20 |         0 |  =20 100000.00
 12 | 2005-04-16 02:46:02+08 | 01.2010100.00002=20 |         0 |   =20 20000.00
 13 | 2005-04-16 02:46:59+08 | 01.2010100.00002 | = 163000.00=20 |           = 0
 14 |=20 2005-04-16 02:50:17+08 | 01.2010100.00005=20 |         0 |  =20 100000.00
 15 | 2005-04-16 02:53:42+08 | 01.2010301.00001=20 |         0 |  =20 100000.00
 16 | 2005-04-16 02:57:22+08 | 01.2010100.00001=20 |         0 | =20 2000000.00
 17 | 2005-04-16 23:56:44+08 | 01.2010200.00000=20 |         0 | = 10000000.00
 18 |=20 2005-04-17 18:58:57+08 | 01.2010100.00003=20 |         0 |  =20 100000.00
 19 | 2005-04-17 19:13:05+08 | 01.2010100.00002=20 |         0 |  =20 100000.00
 20 | 2005-04-17 19:13:45+08 | 01.2010100.00002=20 |         0 |  =20 200000.00
 21 | 2005-04-17 19:15:36+08 | 01.2010100.00002=20 |         0 |   =20 50000.00
 22 | 2005-04-17 19:17:17+08 | 01.2010100.00005 |  = 10000.00 |          =20 0
 23 | 2005-04-17 19:18:06+08 | 01.2010100.00004=20 |         0 |  =20 200000.00
 24 | 2005-04-17 21:45:31+08 | 01.2010100.00002=20 |         0 = |    =20 9000.00
 25 | 2005-04-17 22:16:08+08 | 01.2010100.00006=20 |         0 |  =20 100000.00
 
 
my problem, i would like to place=20 balance in last field. i try using this query :
 
SELECT
  trans.id,
 =20 trans.trx_timestamptz,
  trans.account,
  = trans.debet,
 =20 trans.credit,
 

  (SELECT
     =20 SUM(coalesce(credit,0)-coalesce(debet,0))
   FROM
  = (
    SELECT
     =20 transactions.id,
     =20 transactions.trx_timestamptz,
     =20 transactions.account,
 
     =20 CASE
        WHEN = trx_type.t_type  =3D=20 'DB' THEN
         =20 transactions.amount
       =20 ELSE
         =20 0
      END AS=20 debet,
      = CASE
     =20 WHEN trx_type.t_type =3D 'CR'=20 THEN
         =20 transactions.amount
       =20 ELSE
         =20 0
      END AS credit
   =20 FROM
     =20 transactions
      INNER JOIN trx_type ON=20 (transactions.trx_type_id =3D trx_type.id)
 
  )  sub
   WHERE
   =20 (sub.trx_timestamptz <=3D = trans.trx_timestamptz)
   =20 AND
    (sub.account =3D trans.account)
  = )
  AS=20 balance
 

FROM
  (
   =20 SELECT
     =20 transactions.id,
     =20 transactions.trx_timestamptz,
     =20 transactions.account,
 
     =20 CASE
        WHEN = trx_type.t_type  =3D=20 'DB' THEN
         =20 transactions.amount
       =20 ELSE
         =20 0
      END AS=20 debet,
      = CASE
     =20 WHEN trx_type.t_type =3D 'CR'=20 THEN
         =20 transactions.amount
       =20 ELSE
         =20 0
      END AS credit
   =20 FROM
     =20 transactions
      INNER JOIN trx_type ON=20 (transactions.trx_type_id =3D trx_type.id)
 
  ) AS trans
 
-----
 
everything is ok, but when record > 1000000 that query eat all = my cpu=20 process and take a long time, i have wait for 3 mimutes=20 but query doesn't finish. (pgsql-8.0-1 running on Dual = Xeon 2.8=20 and 2GB of RAM)
 
can you help me how to fix them.
Can I using function to fix them (how?)
 
Thanks
 
 
 
 


 
------=_NextPart_000_032D_01C55EC8.01C1B7B0--