Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access table (Access 2000) that contains phonelogs. These logs are
compiled with Date (dd/mm/yyyy), Time of Day (hh:mm:ss), Start of
conversation (dd/mm/yyyy hh:mm:ss), Duration (hh:mm:ss).

I want to know how many lines (we have 10) are occupied in a particular
timeslot. For instance, if PersonA started his conversation at 27/10/2004
09:01:00 to 09:02:33 and PersonB at 27/10/2004 09:02:00 to 09:10:22 then they
occupied 2 lines for a brief moment as they times clashed.

How can I do these stats using SQL query or programming please?

skc
 
Dear SKC:

This is a VERY nice problem. I have a solution to a related problem
on which I have not yet published. I've started thinking about how to
adapt this technique to your problem, and I'm sure it can be done.

In a preliminary evaluation, I've come to the conclusion that the
database engine will need to consider an N-way cross product of the
phone calls. We need to minimize N. In the simplest method, N would
be all the phone calls. That would create an impossibly large cross
product for a significant number of phone calls. The problem of
banding this into a lower number could be explored.

At the close of my preliminary exploration of the problem, I have to
suggest that a solution occurs to me that is, initially, not a set
approach to it at all, but a procedural one. If a recordset is
prepared in chronological order, then each call could be assigned to
the first of the 10 lines, keeping the time for the end of the call in
an array of 10 variables. As each subsequent phone call is
considered, it can be assigned to the first open phone line. As each
phone call is considered, the array can be scanned for calls that have
ended by the starting time of the new phone call, and all such calls
"closed" in the array before searching for the first open line for the
current call.

By recording the "line number" of each call during this scan of the
data, you could then search for the largest line number used. This
would be the answer to your question. The "largest line number" could
also be maintained in the code as it runs, but the possibilities for
analysis of the results would be much less, although your basic
question would still be answered.

Your simple looking question appears to me to be something in the
order of a classic question of considerable interest and import.
Maybe I'm missing some obvious simple solution. Or maybe this is a
problem that will puzzle the experts for some time to come.

Thanks for the challenge!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Skc said:
I have an Access table (Access 2000) that contains phonelogs. These logs are
compiled with Date (dd/mm/yyyy), Time of Day (hh:mm:ss), Start of
conversation (dd/mm/yyyy hh:mm:ss), Duration (hh:mm:ss).

I want to know how many lines (we have 10) are occupied in a particular
timeslot. For instance, if PersonA started his conversation at 27/10/2004
09:01:00 to 09:02:33 and PersonB at 27/10/2004 09:02:00 to 09:10:22 then they
occupied 2 lines for a brief moment as they times clashed.

How can I do these stats using SQL query or programming please?
Click to expand...


Without actually trying it, the crude, but straight forward
approach that pops to my mind is to create a table as a base
ID for each time slice. It looks like you have a one second
resolution for your data, so create a table with one time
field. Since there will be 80,000+ records, use a simple
code loop to populate each record with the time of day in
one second intervals (no date part).

Then create a query to calculate the count of active calls
for each second in the day:

SELECT Date() + Times.Second As TOD,
Count(*) As BusyLines
FROM Times INNER JOIN PhoneLog
ON Date() + Times.Second BETWEEN PhoneLog.Start
AND PhoneLog.Start + PhoneLog.Duration
GROUP BY Date() + Times.Second
 
Dear SKC:

A solution has come to mind that is direct and simple.

I was thinkinking along the lines of using a query to do "sampling" of
the data at various time intervals. This is sort of an engineering
approach (in my mind). If you use tighter and tighter sample
intervals you'd eventually get the answer in the limit.

The problem is that this may get bad before it gets accurate.

A good part of the work is to build another table with the sampling
intervals. Against this table you would run a query that counts phone
calls that are BETWEEN the start and end times of each interval.

Then I realized we already have the perfect but minimal set of times
against which to run it. Rather than use some regular interval and
have a lot of them, we can just use the time of the start of every
call you have. We would then be producing the count of the number of
active calls at the moment each call begins!

Please get specific about the columns in your table and give examples
of the data they contain. I think we can whip this problem quickly
now.

It's still a pretty nice problem, but isn't going to be some monster
to code. Pardon my earlier rumblings, if you please. I had to start
thinking about this somewhere, and posting my musings can be a good
way to get the juices flowing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top