I have been running a system for over ten years now that manages callers
at drop-in type offices for councils, Inland Revenue and the like. The
main tables are fairly simple. TICKET has a record for each ticket
issued ( nowadays this includes tickets for telephone inquiries and
correspondence but that does not affect things ). TICKET_ID identifies
each ticket, and a second table TRANSACTIONS stores the 'movement' of
that ticket round the system. Waiting on a queue, serving at room, being
processed by back office etc. We can work out waiting and serving times
quite happily.

The other type of report produced is performance. The number of people
waiting, average waiting time, max waiting time at 15 minute time slots
during the day. This has been generated by a program that runs the
system actually logging a complete set of numbers every 15 minutes, and
producing a STATS table which can then be displayed, giving a daily or
period view of the 'performance'.

The problem now is that we need to expand things and make things more
flexible, and while I could simply extend the STATS table, it is still
restricted to the numbers that are stored there. So what is needed is
some way of creating the data from the TICKET and TRANSACTION file.
Which is where I have been going round in circles :(

This probably needs to build a temporary table of results - like STATS -
and populate that before looking at producing the graphs for 'load'
during the day ( and over week an month periods ). The problem I'm
having is how to generate a 'snapshot' of the state of each ticket at a
fixed time each day. The ticket has a 'start' and 'stop' time so we know
when it is 'active' but part of the problem is that the ticket date is
stored in that field as well. Also the problem is selecting the
transaction record that matches that time slot. MAX(TRANSACT) WHERE
TRANSACT < 'time' AND TICKET_ID = X but I need the other values for that
record.

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!

Main reason for asking is that I find that simply writing the problem
down often helps, but I need a kick into other methods of doing this ;)

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php