Any DBAs in the house?

[mofo]

  I have a client who wants to display the average answer speed for all the calls coming in for the day per that queue (there are four queues)
 
  The data, to be displayed correctly, is in two tables. Anway... I know there has to be a COUNT somewhere to count the amount of calls for that queue and then I have to sum the queue times and divide by the sum of calls.
 
  So, I'm really not that good with SQL, I guess I just haven't needed to ever do anything like this before. Anywho - I think I would have to declare some variables and maybe make a stored proceedure
 
  This is as far as I got:

SELECT
    resourceGroupName,
    queueTime,
    startDateTime
  FROM
    ResourceGroup,
    ContactRoutingDetail
  WHERE
    (ResourceGroup.ResourceGroupID = ContactRoutingDetail.rsrcGroupID) AND
    (queueTime > 0) AND
    (startDateTime >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)) AND
    (startDateTime < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1))
  ORDER BY
    resourceGroupName

pleh - I'm probably way off here...  any help would be cool.

thx

[Kjuib]

The Names and faces have changed to protect the logic...

Code:

SELECT queue.name AS queueName, AVG(queue.endtime - queue.starttime) AS avgTime, COUNT(queue.id) AS callCount, queue.date AS queueDate

FROM resourcegroup
  INNER JOIN contactroutingdetail ON (resourcegroup.resourcegroupid = contactroutingdetail.rsrcgroupid)

WHERE queue.date BETWEEN somestartdate AND someenddate

GROUP BY queue.name, queue.date
ORDER BY queue.date, queue.name

Doing an Inner join while speed up your query.. (But if the tables are under 100,000 records you probably wont notice)
That is the basic layout though, you might have to do some datatype changes.

Personally I hate putting in a timestamp field where the date is going to be queried... I would much rather have 2 fields, one for date and one for time. It makes the SQL a lot simplar.

[mofo]

Kjuib - Thanks for the help. The only problem is that there isn't a queue start and end time. The queue time is just a number.

here is what the tables look like:

[ResourceGroup]
resourceGroupID   profileID   resourceGroupName   active
3                         1                Help Desk              True   


[contactRoutingDetail]
sessionID    rsrcGroupID   queueTime   startDateTime
66000057523      4               1      4/21/2007 11:45:02 PM


Thanks again man.

[mofo]

Nevermind - figured it out... Thanks!! again for your help.


-mike

[mofo]

Deerrrr...

OK... I'm retarded. I explained it wrong... I already had this number. I was looking for the "% calls answered in 20 seconds".

I'm gonna try again.

[mofo]

More I try - the more I realize i suck at SQL...

Anybody know why this doesn't work --

(COUNT(queueTime) < '20') / COUNT(sessionID) AS theNumber

I'm getting this --

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near '<'.

[Kjuib]

the COUNT(queuetime) is less then '20' ? that wont get you a number that you are then trying to devide by COUNT(sessionID)...
the < should probably go into the where...

[mofo]

I finally got it working completely right

--final query

SELECT
  CSQName,
  convOldestContact,
  loggedInAgents,
  availableAgents,
  callsWaiting,
  callsHandled,
  totalCalls,
  callsAbandoned,
  convAvgWaitDuration,
  endDateTime,
  COUNT(sessionID) AS thecalls
FROM
  RtCSQsSummary
  INNER JOIN ResourceGroup ON (RtCSQsSummary.CSQName = ResourceGroup.ResourceGroupName)
  AND (ResourceGroup.resourceGroupName = RtCSQsSummary.CSQName)
  INNER JOIN ContactRoutingDetail ON (ResourceGroup.resourceGroupID = ContactRoutingDetail.rsrcGroupID)
WHERE
  (ResourceGroup.ResourceGroupID = ContactRoutingDetail.rsrcGroupID) AND
  (queueTime < 20) AND
  (ContactRoutingDetail.startDateTime >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)) AND
  (ContactRoutingDetail.startDateTime < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1))
GROUP BY
  CSQName,
  convOldestContact,
  loggedInAgents,
  availableAgents,
  callsWaiting,
  callsHandled,
  totalCalls,
  callsAbandoned,
  convAvgWaitDuration,
  endDateTime

--end

I am taking the count (where queue time < 20) and dividing it by total calls to give me what I need (via. actionscript).

Thanks for your help Kjuib.