> I think I need to create a table with a set of records for
> each time slot, where each record is a ticket active at that
> time. Losing the date information in the process and giving
> me something that can then be 'turned through 90o and
> displayed as performance'.

> Anybody playing with similar queries/reports - any ideas!

I imagine that the separate timeslots table is considered
necessary in order to make reporting performance acceptable.
If that is the case then I have a similar(ish) problem with
a high volume transaction table.

My solution at the moment is a sort of cache table (my report
requirements are quite different to yours) but what I have
been considering for a future version is an "index table"
system rather than the duplication of data I am doing now.

In your case I imagine each index entry would point to the
ticket and transaction record applicable at that instance.

It seems (from what I understand in your email) that the ideal
would be to create the index table so that there is minimal
duplication of data. Each index would mark ticket transition,
rather than try to mark the state of all tickets at every
interesting point in time. A join would provide a very direct
access to the actual detail from the original data.

Lets say that your minimum report interval was 15 minutes:
Without trying to think through the detail, I wonder if it may
be possible to create an index on the "index table" (that has
been created as a sort of transition log) that can report
counts per interval by creating an indexed field that is
generated from the timestamp based on 15 minute intervals.
That is; if there is a field that increments with each 15
minute interval then you have various group-by and distinct
report possibilities that can be optimised by index.

The _hope_ being that you can restrict the rows actually
accessed by a given report by explicitly directing such access
through the index table.

Just a thought, it may or may not help.

--
Geoff Worboys
Telesis Computing