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
