Help needed with finding next date in a series!!!

  • Thread starter Thread starter chantal.lyn
  • Start date Start date
C

chantal.lyn

Hi there - I hope someone can help - I don't use loops much, so I'm
stumped on something which is probably very basic and straightforward.

I've got a set of data covering a 3-month period, provided by BT on
calls made to a helpline number. The fields call all the usual stuff -
encrypted IDs for each number, date and time of call, if it was
answered or diverted to a busy message, length of call. I need to
produce some stats on breakdown of lost (unanswered) calls vs answered
calls per caller. In addition to the source table, I've created a
ztblMain which contains unique phone 'numbers', and a count of calls
answered and calls lost, and another two ztbls of the phone numbers and
date/times their calls were lost or answered.

As it's a popular helpline, a lot of callers call more than once in
this period (the most extreme was one number calling 726 times in this
timeframe, getting through 381 times).

So as I need to calculate an average of how many times callers try the
helpline before they answer, I figure what I need is a loop to step
through the records and find the next available date/time (merged into
one field) after each answered call (I've created Lost and Unanswered
yes/no fields) per phone number - my idea was to use the ztbl for
answered call date/times as a source table for this, populate a field
with the initial call in the series, and then do a count on the calls
between the two. I just have absolutely no idea how to go about it - as
I said, I rarely work with loops - can anyone help?

Huge huge thanks if you can!!

Chantal
 
Hi Chantal,

I have a sneaking feeling that with the right DB design (or even directly
from the BT supplied data) you can get SQL to do all the hard work for you
and avoid loops altogether. Of course provided that your result sets are
ordered by date, time 'looping' is a feasible option.

I'm not too clear about some of your concepts. I understand that 'A
Session' (for want of a better term) consists of a number of unsuccessful
calls terminated by a successful (answered) call. This would be all that's
needed if all the callers persisted until they made a successful call but I
suspect some/many give up. Furthermore if a caller makes a number of
unsuccessful attempt on day 1 and then on day 2 makes more unsuccessful calls
followed by a successful call can you be sure that all calls belong to the
same session. Maybe you're dealing with two sessions.

Anyway the bottom line for what I'm trying to say is that you must
set/declare precise unambiguous rules and definitions before launching into
solutions.

Another point is to question how clean is the data supplied by BT. From my
experience there is always some level of corrupted data returned from a
switch. If you're not seeing this then perhaps BT is cleaning and filtering
the data before sending it on.

As to the immediate question that prompted your post. Derive the next date
by adding the call duration plus (say) one second to the date/time of the
current call. Use DateAdd for this.

Regards,
Rod
 
Rod, huge thanks for that.

I'm dealing with two people on this, and they both seem to want very
different things - and fairly non-specific, too. Ultimately, they want
to be able to calculate how many helpline staff they need to recruit,
given the number of unanswered calls and callers who just give up.

The data's not too bad, given the BT data I've dealt with in the past,
although there's been some concern about how to treat calls of less
than 1 second in length - 0.1 seconds up to 0.9.

The person in charge of the data should be back at work tomorrow, so
I'll pick his brains, and thanks for the DateAdd tip, I'll see how I
get on with it.
 
Dealing with two people with different requirements and neither of them very
specific? Welcome to my world! :-)

At least the ultimate objective is understood. Personally I would ask, if
you dare, what the optimum target service levels should be. For example: 'We
shall strive to answer each call within XX minutes.' This at least gives a
quantified standard for reviewing the data. Calls less than a second? I
would count them until someone demonstrates I should not unless they are
individual isolated calls with no repeats in a specified period.

Some years ago I spent time in and around International Settlements for a
large telco so I know all about dirty call data. As a percentage the dirty
data is very small but when dealing with vast numbers of calls you can still
end up with thousands where the A number, the B number or the duration is
missing. The most common is the last and occurs when the switch fails to
'see' the call termination.

Yes, by all means get back to me.

Rod
 
Back
Top