> 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

I would consider an active table, a history table and a summary table.
Active ticket transactions are in the active table, when tickets are
completed or at month's end I would move the transactions into the history
table. At this time I would run analysis routines on them to insert into the
summary table.
I would also have year-end processes which move history tables into the data
into the appropriate year table.

I use this process for accounting data. It works well with people who like
month-end, year-end movements and provides manageable size units to archive
without disturbing current views.

Alan