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