Hi Lester

I don't know if I am understanding your problem correctly but I would do
something like...

Split day into 15 min periods (numbered 1 - 96). Write a stored
procedure (SP1) that returns the transaction ID of each transaction that
falls within a specified slot / day. In your SP you would also return
the turnaround time since you know it as well.

I would then create another SP (SP2) that produced results by a
specified time period. SP2 would simply calc all the time periods /
days required for the specified date range then call SP1 for each slot,
joining whatever other tables are required for your report.

If your were using Crystal you could massage your SP so it returned all
the fields required for the report. It would probably be quick. We
have similar reports on similar sized datasets that are quick. Also
avoids your stats table at all and is far more flexible.

I hope this is some help and Im not to far off target.

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Lester Caine wrote:

> 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 ;)
>
>