On Wed, 23 Feb 2005 23:12:41 -0000, Lloyd Thomas
<[EMAIL PROTECTED]> wrote:
> Dennis,
> Thanks for you help so far. I think it is easier for PHP to select
> the MAX event. The problem I now have is if there is no records for an hour,
> PHP will through up an error because MAX must have at least one record to
> process, even if it is 0.
>
Lloyd,
You can force a zero record if there no events using a union like this:
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
union
select 0, 0 where not exists (select * from event_data)
This will give a single row with a count of zero if there are no
events, or the usual set of 60 rows if there are one or more events.
Dennis Cote
