Revisted your query. I was being a bit lazy. changed 'select minute, max(events)' to 'select minute, events' and added 'order by minute' to give me what I needed.
Thanks
----- Original Message ----- From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, February 23, 2005 5:41 PM
Subject: Re: [sqlite] tricky date time problem
Lloyd,
I messed up the math for the end minute calculation. :-[
The correct query is given below. Adding 59 was intended to round the result of an integer division (which drops the remainder), but I'm actually keeping the reminader and throwing away the quotient so it was simply wrong.
select minute, max(events)
from
(select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
begin_minute <= minutes.i and minutes.i <= end_minute
else
begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute)
HTH Dennis Cote
