From: Partha Guha Roy <partha.guha.roy@gmail.com>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need to subtract values between previous and current row
Date: Fri, 15 Dec 2006 19:21:36 +0600
Message-ID: <6bdfe880612150521n137d80f7q463955f26db1cef0@mail.gmail.com> (raw)
In-Reply-To: <297264.3730.qm@web31806.mail.mud.yahoo.com>
References: <6bdfe880612150427o19bfe90ch87a972c0f5898ffb@mail.gmail.com>
<297264.3730.qm@web31806.mail.mud.yahoo.com>
Hi,
Thanks for everyones email. Let me clarify a little bit more.
Actually there is a state change of an entity. now the results are stored in
the following manner:
CID ATIME STATE
101 12/10/2006 1
101 12/12/2006 2
101 12/14/2006 1
101 12/17/2006 2
102 12/14/2006 1
102 12/16/2006 2
102 12/18/2006 3
Now I have to find which CID stayed on which STATE for how long.
Lets assume today is the 20th.
So the result would be:
CID DURATION STATE
101 2 1
101 2 2
101 3 1
101 3 2
102 2 1
102 2 2
102 2 3
The constraints are:
I can't use any plpgsql or other function.
There can be gaps in CID.
No sequence can be created.
Thanks.
On 12/15/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> >
> > ID ATIME (MM/dd/yyyy)
> > == ====
> > 1 10/12/2006
> > 2 10/14/2006
> > 3 10/18/2006
> > 4 10/22/2006
> > 5 10/30/2006
> >
> > Now I need a query that will subtract atime of row 1 from row 2, row2
> from
> > row3 and so on...
>
> just an idea.
>
> select (A.atime - max(B.atime)) duration
> from table A join table B
> on (A.atime > B.atime)
> group by A.atime;
>
> Regards,
> Richard Broersma Jr.
>
--
------------------------------
Partha Guha Roy
***********************************************
Take everything you like seriously, except yourselves.
- Rudyard Kipling
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: partha.guha.roy@gmail.com, rabroersma@yahoo.com
Subject: Re: Need to subtract values between previous and current row
In-Reply-To: <6bdfe880612150521n137d80f7q463955f26db1cef0@mail.gmail.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