> 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
select A.cid, (A.atime - max(B.atime)) duration, A.state
from table A join table B
on (A.atime > B.atime and A.cid = B.cid)
group by A.atime, A.cid, A.state;