Re: [sqlite] tricky date time problem

[Lloyd Thomas]

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.

Thanks again
----- Original Message ----- 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, February 23, 2005 8:51 PM
Subject: Re: [sqlite] tricky date time problem


Dennis Cote wrote:
I though you wanted the minute with the most events. I added the outer 
select to show that that step can be done easily in the same query, 
rather than relying on PHP to extract this info from the full results for 
the hour.
But of course that portion of the query doesn't work the way I wanted it 
to. This is a common problem with all SQL aggregate functions. They 
generate tables with their won rows, they don't return rows fromt the 
table they are summarizing.

To do what I want you need to build a temp table or recalculate the event 
count table twice (because SQLite doesn't support named subqueries). You 
then need to find all the minutes with counts that match the maximum value 
because there could be more that one minute with the same count value.

Using a temp table:
create temp table t as
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

select minute, events
from t
where events = (select max(events) from t)
order by minute
drop table t

Or using repeated sub-queries (basically replace each instance of t in the 
above query with the entire query used to build the event count table):

select minute, 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
) where events = (select 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
)) order by minute

The second has the advantage that it is a single (complex) query, but 
neither is really pretty, so perhaps using PHP isn't such a bad idea.

P.S. Richard, this is another example of where named subqueies and the 
WITH clause allow more efficient SQL to be written. This could be stated 
as below if this feature was supported by SQLite.

with
   event_counts as (
       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 )
select minute, events
from event_counts
where events = (select max(events) from event_counts)
order by minute