On 01-10-10 22:30, Igor Tandetnik wrote:
> 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
>
your query returns:
001|c|d
001|e|g
001|f|g
so i tried, and come up with this:
select e.id, e.date, a.date
from events e
left join events a on e.id=a.id and e.date<a.date and a.status='N'
where (select status from events p where p.date < e.date order by date
desc limit 1)='N' and e.status='Y'
and (select count(*) from events where date between e.date and a.date
and status='N')=1;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
