Re: Need to subtract values between previous and current row - Mailing list pgsql-sql

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,0from 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 |     1101 |        2 |     2101 |        3 |     1101 |        3 |     2102 |
    2 |     1102 |        2 |     2102 |        2 |     3
 
(7 rows)


gnari