Andy Chambers <achambers.h...@gmail.com> wrote: > Given the following.... > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into events values ('001','d','N'); > insert into events values ('001','e','Y'); > insert into events values ('001','f','Y'); > insert into events values ('001','g','N'); > > Is it possible, using plain SQL, to derive the following > > 001,c,d > 001,e,g > > i.e. an "N" in the third column means event "001" has stopped, and a > "Y" means it > has started back up again. Note that because the status immediately > preceding "f" > is also "Y", there is no corresponding row in the output
select Start.id, Start.date, Stop.date from events Start join events Stop on (Start.id = Stop.id and Start.date < Stop.date) where Start.status = 'Y' and Stop.status = 'N' and not exists (select 1 from events Middle where Middle.date > Start.date and Middle.date < Stop.date and Middle.status = 'N'); Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users