Lloyd Thomas wrote:
I have two tables the event table which holds the data and a table which has an entry for each minute
CREATE TABLE event_data ( call_id INTEGER PRIMARY KEY, desc varchar(32) NOT NULL default '', event_time datetime default NULL, stamptime integer default NULL, duration integer default NULL ); 'stamptime' is the unix time (Number of seconds
since 1970) representation of 'event_time'
'duration' is the duration of the event in seconds
CREATE TABLE integers60 ( i INTEGER );
I have a query which calculates the number of events during an hour by the
minute. It needs to work out which minute has the most events and the
average events during that hour. So it should return an array of 60 results
for an hour where I can use the MAX() feature in php to find the peak
minute.
Lloyd,
Assuming that no events have a duration longer than 1 hour (because then the math gets more complicated) I think this should do what you want. Note, there may be typographical errors here since I don't have your database to run this query against for testing, but the query does compile.
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 + 59) % 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)
Basically it calculates the start and end minute for each event and then does an outer join with your minute table. This produces a cross product of all minuets in and hour. For each row in the cross product it checks if the minute is between the begin and end time in a circular fashion. If so that row is selected. Then it groups the rows by minute and counts the records. This is produces a table of 60 rows with event counts. Finally, it selects the row with the highest event count. I have assumed that your event_time is an integer count of seconds from some epoch (i.e. a Unix time value).
HTH Dennis Cote
