From: Steve Crawford <scrawford@pinpointresearch.com>
To: Muhyiddin A.M Hayat <middink@indo.net.id>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calc
Date: Thu, 16 Oct 2003 10:35:35 -0700
Message-ID: <200310161035.35762.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <001101c3940c$29f004e0$1f00a8c0@middinkcomp>
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
<200310151331.22516.scrawford@pinpointresearch.com>
<001101c3940c$29f004e0$1f00a8c0@middinkcomp>
On Thursday 16 October 2003 10:37 am, Muhyiddin A.M Hayat wrote:
> > Something like this:
> >
> > select id,db,cr,(select sum(cr-db) from calc sub where sub.id <=
> > calc.id) from calc;
> >
> > This of course assumes that ID indicates the correct order of the
> > entries and it will blow up if you allow nulls for cr or db
> > (which you shouldn't since that would literally be interpreted as
> > "they withdrew 10 and deposited an unknown amount"). If you have
> > null values already and they should be interpreted as 0 just do
> > this:
> >
> > select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0))
> > from calc sub where sub.id <= calc.id) from calc;
> >
> > I assume no responsibility for potential lack of scalability of
> > this query. :) It's quite possible a faster solution exists -
> > we'll see what comes up on the list.
>
> If data from "View" without ID, how can I do?
>
> My View:
>
> trx_date | trx_time | descriptions |
> payment_method | debet | credit | creator
> ------------+----------+--------------------------------------+----
>--------- ---+-------+----------+---------
> 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 |
> Visa
>
> | 3800 | | middink
>
> 2003-10-16 | 03:28:30 | Payment - Thank You |
> Visa
>
> | | 40000.00 | middink
>
> 2003-10-08 | 18:17:40 | Payment - Thank You |
> Cash
>
> | | 50000.00
The id field only keeps the transactions in the correct order so you
can sum the previous transactions. You can do the same thing with
your date and time fields (though using a single datetime field would
be easier). A caution, though: you will have trouble if two
transactions share the same date and time.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, middink@indo.net.id
Subject: Re: Calc
In-Reply-To: <200310161035.35762.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox