Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id AC8EF9FA474
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 12:06:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 96232-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Dec 2006 12:06:01 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mx3.ipf.is (man.ipf.is [85.197.192.163])
	by postgresql.org (Postfix) with ESMTP id AE3BE9F9B05
	for <pgsql-sql@postgresql.org>; Fri, 15 Dec 2006 12:06:00 -0400 (AST)
Received: (qmail 22306 invoked by uid 1010); 15 Dec 2006 15:59:19 +0000
Received: from 88.149.106.19 by man (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.5/4.5):.
	Processed in 3.811707 secs); 15 Dec 2006 15:59:19 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via man
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.5/4.5):.
	Processed in 3.811707 secs Process 22273)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx3.ipf.is with SMTP; 15 Dec 2006 15:59:15 +0000
Subject: Re: Need to subtract values between previous and current row
From: Ragnar <gnari@hive.is>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: Partha Guha Roy <partha.guha.roy@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <519101.72709.qm@web31802.mail.mud.yahoo.com>
References: <519101.72709.qm@web31802.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Fri, 15 Dec 2006 16:06:56 +0000
Message-Id: <1166198816.6369.179.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.1 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/117
X-Sequence-Number: 27073

On fös, 2006-12-15 at 06:01 -0800, Richard Broersma Jr wrote:
> > 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;

not bad, except you miss the initial state for each cid,
and I do not see how you get the final duration.

this inspired me:

test=# create table t (cid int, atime date, state int);
CREATE TABLE
test=# insert into t values (101,'2006-12-10',1);
INSERT 0 1
test=# insert into t values (101,'2006-12-12',2);
INSERT 0 1
test=# insert into t values (101,'2006-12-14',1);
INSERT 0 1
test=# insert into t values (101,'2006-12-17',2);
INSERT 0 1
test=# insert into t values (102,'2006-12-14',1);
INSERT 0 1
test=# insert into t values (102,'2006-12-16',2);
INSERT 0 1
test=# insert into t values (102,'2006-12-18',3);
INSERT 0 1
test=# select A.cid, 
              (min(B.atime)-A.atime) as duration,
              A.state 
       from t as A
            join (select * from t
                   union all
                  select distinct on (cid) cid,
                  '2006-12-20'::date,0 from t
                 ) as B 
                 on (A.atime < B.atime and A.cid = B.cid)
       group by A.atime, A.cid, A.state
       order by a.cid,a.atime;

 cid | duration | state 
-----+----------+-------
 101 |        2 |     1
 101 |        2 |     2
 101 |        3 |     1
 101 |        3 |     2
 102 |        2 |     1
 102 |        2 |     2
 102 |        2 |     3
(7 rows)


gnari