Simple call log feature query

Mark HHP

Hey guys

In my company the Consultants have what we call "power hour" every day which is one hour in which they are meant to make a certain amount of phone calls.

SO I currently have this query:

FIND Calls WHERE DATE_PART(Calls.TimeOfCall)=DATE_PART(CURRENT_TIMESTAMP) AND HOUR(Calls.TimeOfCall)>=9 AND HOUR(Calls.TimeOfCall)<=10 ORDER BY Calls.TimeOfCall

That's fine, it all works as it should and I've set it to display the summary next to each Consultants name so that you can see at a glance that Consultant X made 5 phonecalls and Consultant Y made 10 phonecalls.

Now, I want to add a section on the dashboard that counts the amount of Calls and then displays the best and worst Consultant in terms of Calls.

For instance:

The consultant who made the most calls is X with Y amount of calls.
The consultant who made the least amount of calls is Z with A amount of calls.

Similar to the bug fix example. I'm just not that au fait with the COUNT function (I assume that's what I'd use?) Like a COUNT mixed with order and take best or what?

The aim with this is to foster a sense of competition (and shame) in the Consultants and push them harder.

Rennur

You probably need 2 separate queries for the Consultant Object.

Most calls query:

1) New Query > Name: Most Calls > Business Object: Consultant

You could specify a 'form' to display the result. The form section in the Consultant object can just be plain HTML. eg.

<<Consultant.Name>> is leading with <<Consultant.NumberOfCalls>> calls!

2) Tick 'Take best' 1 items

3) Sort Consultant.NumberOfCalls Descending

4) Dislay Results:

Display 1 items per page

5) On your VP panel, show result of a query 'Most Calls'. This will display the Consultant A is leading with 20 calls! in plain HTML.

For the Least Calls just create a new query with Ascending sort order and create a new form in the consultant object.

<<Consultant.Name>> is struggling with only <<Consultant.NumberOfCalls>> calls!

Rennur

Correction:

Skip do Step 2, don't tick this box.

Mark HHP

How would I apply the existing query to the query I already have? I only need the most calls per day between 9 and 10?

FIND Calls WHERE DATE_PART(Calls.TimeOfCall)=DATE_PART(CURRENT_TIMESTAMP) AND HOUR(Calls.TimeOfCall)>=9 AND HOUR(Calls.TimeOfCall)<=10 ORDER BY Calls.TimeOfCall

That's my existing query (named PowerHourCalls), I need to pull in the NumberOfCalls value daily.

Should I run a process firstly that:

a) Clears the NumberOfCalls
b) Runs this query, and somehow pushes the number of calls to Consultant.NumberOfCalls
c) Then runs the two queries you've described

I feel like my brain is almost there but I'm busy juggling a bunch of other things as well.

tford

I would add a BO CallSummary and create instances of CallSummary for each caller. Attributes would include the buckets you need to calculate like total calls & power hour.

Then your queries would extract the info appropriate for each query ... most, least, totals, etc.