Hi Lester!

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.

So far your essay is pretty Greek to me, I don't speak any Greek and my
first ever trip to Greece is still one week away.

Though I guess I'm more of the type to settle for permanent than
temporary tables - at least until Firebird 2.0 - and your structure
seems fairly fixed. So why not have a table that you populate whenever
your request changes (a generator could be used to get a unique ID for
each request, deletes are then optional)?

> 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.

Ah, finally some SQL - and the SELECT dialect is my favourite!

WHERE NOT EXISTS(SELECT * FROM TRANSACTION T2 WHERE T2.TRANSACT >
T1.TRANSACT AND T2.TRANSACT < 'time' AND T2.TICKET_ID = X)

(at least if TRANSACT is unique for each TICKET_ID).

> 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!

Sorry, I've never done anything similar.

> 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

HTH,
Set