Allen Fowler wrote:
> # Show all events and duration:
> sqlite> select *, (strftime('%s', end) - strftime('%s', start)) as
> length from events;
> id name kind
> start end length
> -------------------- -------------------- --------------------
> -------------------- -------------------- ---------- 1
> tom hour 2009-06-13 11:00:00
> 2009-06-13 12:00:00 3600 2 tom
> hour 2009-06-13 09:00:00 2009-06-13 10:00:00
> 3600 3 joe hour
> 2009-06-13 09:00:00 2009-06-13 10:00:00 3600 4
> tom day 2009-06-13 00:00:00
> 2009-06-14 00:00:00 86400
>
>
> #
> # And now, I want to get a result table with one row per user showing
> the "shortest active at 9:30 AM event" for each user.
select * from events e1
where id in (
select id from events e2 join
(select datetime('now', '+1 day','start of day', '+9 hours','+30
minutes') x)
where e1.name = e2.name and start < x and end > x
order by (julianday(end) - julianday(start)) limit 1
);
The variant below is more complicated, but may run faster:
select * from events e1
where id in (
select (
select id from events e2
where e2.name = names.name and start < x and end > x
order by (julianday(end) - julianday(start)) limit 1
)
from (select distinct name from events) names join
(select datetime('now', '+1 day','start of day', '+9 hours','+30
minutes') x)
);
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
