Re: Need to subtract values between previous and current row

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(at)yahoo(dot)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