cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
2743
Views
5
Helpful
24
Replies

Combining CT and SG data

Hey,

I would like to combine metrics gathered by both SGs and SGs.

CallsOffered (from any table) and AHT,TalkTime,ReservedStateTime and so on.. (preferrable from the Agent_Skill_Group_Interval table)

But I really can´t find any ID i can combine them between (like SkillTargetID or CallTypeID).

Anyone who has done this before, or has any tips on how i can do it?

24 REPLIES 24
Jameson Gagnepain
Rising star

Robert,

What version of UCCE and CUIC are you on? Versions 8 and up have "Call Type Skill Group" reports already. Take a look at the Call_Type_Skill_Group_Interval table in the Database Schema.

-Jameson

-Jameson

Hi Jameson,

Version 9.1, Thank you for putting me in the right direction!

I added CallsOffered from another table (since CallsOfferedRouted column didnt show the correct values) but I´m getting really weird figures on this column. The correct total should be 22 inbound calls - but I´m getting WAY more than that in this report.

The datetime intervals should be exactly the same on both these tables, so I´m not sure what could be wrong.

Any ideas?

SELECT datetime= CTSG.DateTime,

       calltypeId= CTSG.CallTypeID,

       calltypeName= Call_Type.EnterpriseName,

    answerWaitTime=sum(isnull(CTSG.AnswerWaitTime,0)),

    CallsOffered=sum(isnull(Call_Type_Interval.CallsOffered,0)),

       callsAnswered= sum(isnull( CTSG.CallsAnswered,0)),

       callsHandled= sum(isnull(CTSG.CallsHandled,0)),

       handleTime= sum(isnull(CTSG.HandleTime,0)),

       talkTime= sum(isnull(CTSG.TalkTime,0)),

       holdTime= sum(isnull(CTSG.HoldTime,0)),

       callsOfferedRouted=sum(isnull(CTSG.CallsOfferedRouted,0)),

       callsOfferedNotRouted=sum(isnull(CTSG.CallsOfferedNotRouted,0)),

       overflowOut=sum(isnull(CTSG.OverflowOut,0)),

       aat= sum(isnull(CTSG.TalkTime,0))/ sum(isnull(CTSG.CallsHandled,0)),

       aht= sum(isnull(CTSG.HandleTime,0))/ sum(isnull(CTSG.CallsHandled,0)),

       asa= sum(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0))

FROM Call_Type_SG_Interval CTSG (nolock), Call_Type (nolock), Skill_Group (nolock), Call_Type_Interval (nolock)

Where Skill_Group.SkillTargetID = CTSG.SkillGroupSkillTargetID

and Call_Type.CallTypeID = CTSG.CallTypeID

and Call_Type_Interval.CallTypeID = CTSG.CallTypeID

GROUP BY CTSG.CallTypeID, Call_Type.EnterpriseName, CTSG.DateTime

ORDER BY Call_Type.EnterpriseName, CTSG.DateTime

I would start by doing explicit joins instead of relying on SQL to create the joins for you... You also missed specifying CTSG.DateTime=Call_Type_Interval.DateTime. That specifically is why you've got much higher values than you expect - EVERY Call_Type_Interval row with matching CallTypeID is getting joined and summarized. Here's something for you to start with:

SELECT datetime= CTSG.DateTime,

       calltypeId= CTSG.CallTypeID,

       calltypeName= Call_Type.EnterpriseName,

    answerWaitTime=sum(isnull(CTSG.AnswerWaitTime,0)),

    CallsOffered=sum(isnull(CTI.CallsOffered,0)),

       callsAnswered= sum(isnull( CTSG.CallsAnswered,0)),

       callsHandled= sum(isnull(CTSG.CallsHandled,0)),

       handleTime= sum(isnull(CTSG.HandleTime,0)),

       talkTime= sum(isnull(CTSG.TalkTime,0)),

       holdTime= sum(isnull(CTSG.HoldTime,0)),

       callsOfferedRouted=sum(isnull(CTSG.CallsOfferedRouted,0)),

       callsOfferedNotRouted=sum(isnull(CTSG.CallsOfferedNotRouted,0)),

       overflowOut=sum(isnull(CTSG.OverflowOut,0)),

       aat= sum(isnull(CTSG.TalkTime,0))/ sum(isnull(CTSG.CallsHandled,0)),

       aht= sum(isnull(CTSG.HandleTime,0))/ sum(isnull(CTSG.CallsHandled,0)),

       asa= sum(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0))

FROM Call_Type_SG_Interval CTSG (nolock)

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT JOIN Skill_Group SG (nolock)

ON SG.SkillTargetID = CTSG.SkillGroupSkillTargetID

LEFT JOIN Call_Type_Interval CTI (nolock)

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

-Jameson

-Jameson

Thanks Jameson, that almost fixed it! However, there still is a difference between this new report and the standard reports.. And I have no clue why..

Tried adding DateTime from both tables just to see the intervals are the same - and they are.

Data from the new report:

Data from another (correct) report:

Bring in SG.EnterpriseName to your selection, Group By, and Order By. I think you'll find that many of the DateTime selections delivered calls to 5 different skill groups.

-Jameson

You are right, the calls are delivered to the different skill groups!

I have no use of seeing / summarizing inbound calls by SG though - is it possible to make it only SUM them for 1 Skill group for each Call Type? (Without having to add a parameter and choose the first skill group of each CT)

It's possible to do what you're looking for... it's a little complicated though. Here's a starting point for you:

SELECT

   datetime= CTSG.DateTime,

   calltypeId= CTSG.CallTypeID,

   calltypeName= CT.EnterpriseName,

   answerWaitTime=sum(CASE WHEN A.A=1 THEN isnull(CTSG.AnswerWaitTime,0) ELSE 0 END),

   CallsOffered=sum(CASE WHEN A.A=2 THEN isnull(CTI.CallsOffered,0) ELSE 0 END),

   callsAnswered= sum(CASE WHEN A.A=1 THEN isnull( CTSG.CallsAnswered,0) ELSE 0 END),

   callsHandled= sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsHandled,0) ELSE 0 END),

   handleTime= sum(CASE WHEN A.A=1 THEN isnull(CTSG.HandleTime,0) ELSE 0 END),

   talkTime= sum(CASE WHEN A.A=1 THEN isnull(CTSG.TalkTime,0) ELSE 0 END),

   holdTime= sum(CASE WHEN A.A=1 THEN isnull(CTSG.HoldTime,0) ELSE 0 END),

   callsOfferedRouted=sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsOfferedRouted,0) ELSE 0 END),

   callsOfferedNotRouted=sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsOfferedNotRouted,0) ELSE 0 END),

   overflowOut=sum(CASE WHEN A.A=1 THEN isnull(CTSG.OverflowOut,0) ELSE 0 END),

   aat= sum(CASE WHEN A.A=1 THEN isnull(CTSG.TalkTime,0) ELSE 0 END)/ NULLIF(sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsHandled,0) ELSE 0 END),0),

   aht= sum(CASE WHEN A.A=1 THEN isnull(CTSG.HandleTime,0) ELSE 0 END)/ NULLIF(sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsHandled,0) ELSE 0 END),0),

   asa= sum(CASE WHEN A.A=1 THEN isnull(CTSG.AnswerWaitTime,0) ELSE 0 END)/ NULLIF(sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsAnswered,0) ELSE 0 END),0)

FROM Call_Type_SG_Interval CTSG (nolock)

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT JOIN Skill_Group SG (nolock)

ON SG.SkillTargetID = CTSG.SkillGroupSkillTargetID

LEFT JOIN Call_Type_Interval CTI (nolock)

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

LEFT JOIN (SELECT 1 AS A UNION SELECT 2 AS A) A

ON 1=1

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

In a nutshell, I use the "A" table (which is just the values 1 and 2 in column A) to keep the data summarization from Call_Type_Interval separate from the data summarization for Call_Type_Skill_Group. If you add any more columns from Call_Type_Interval, make sure to enclose them in the same SUM(CASE) logic.

The other way to do this would be by summarizing CTSG and CTI in sub-queries and then joining them together... however, you would have to use an Anonymous Block instead of a standard SQL query in order to do DateTime selection.

-Jameson

-Jameson

Jameson,

Thank you very much!

The query above showed really weird results on every metric, so I decided to go for summarizing CTSG and CTI in sub-queries and it solved everything.

Thank you so much for your help, you saved the day (as always)

Here´s the query in case anyone needs it.

SELECT

CT.EnterpriseName as EnterpriseName,

CTSG.DateTime as DateTime,

SUM(CTI.CallsOffered) as CallsOffered,

SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,

SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,

SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered

FROM

(Select

DateTime= DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

talkTime= sum(isnull(TalkTime,0)),

holdTime= sum(isnull(HoldTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

aat= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0))

FROM Call_Type_SG_Interval

WHERE  CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, CallTypeID) CTSG

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID,

CallsOffered=SUM(ISNULL(CallsOffered,0))

FROM Call_Type_Interval

WHERE  CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY CallTypeID, DateTime

) CTI

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

WHERE  CTSG.CallTypeID IN (:CallTypeID)

AND CTSG.DateTime >= :startDate

AND CTSG.DateTime <= :endDate

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

Just realized I have to add the column SkillGroupSkillTargetID from Call_Type_SG_Interval, since I will have to add the table Agent_Skill_Group_Interval to this report as well. (Need TalkInTime, TalkOutTime, AvailTime, HoldTime, BusyOtherTime, ReservedStateTime)

But as soon as I added SkillGroupSkillTargetID it took data from multiple Skill Groups again...

I did the following on the code below:

Removed SkillGroupSkillTargetID again from the first select

Added one more LEFT OUTER JOIN to the table Call_Type_SG_Interval and added the column 'SkillGroupSkillTargetID' here instead

But I still get CallsOffered from multiple Skill Groups..... Any idea?

SELECT

CT.EnterpriseName as EnterpriseName,

CTSGG.DateTime as DateTime,

SUM(CTI.CallsOffered) as CallsOffered,

SUM(isnull(CTSGG.AnswerWaitTime,0))/ sum(isnull(CTSGG.CallsAnswered,0)) as ASA,

SUM(isnull(CTSGG.AnswerWaitTime,0)) as AnswerWaitTime,

SUM(isnull(CTSGG.CallsAnswered,0)) as CallsAnswered

FROM

(Select

DateTime= DateTime,

CallTypeID= CallTypeID

FROM Call_Type_SG_Interval

WHERE CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, CallTypeID) CTSG

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

talkTime= sum(isnull(TalkTime,0)),

holdTime= sum(isnull(HoldTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),

SkillGroupSkillTargetID

FROM Call_Type_SG_Interval

WHERE CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, CallTypeID, SkillGroupSkillTargetID

) CTSGG

ON CTSG.CallTypeID = CTSGG.CallTypeID AND CTSG.DateTime = CTSGG.DateTime

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID,

CallsOffered=SUM(ISNULL(CallsOffered,0)),

TalkTime = SUM(isnull(TalkTime,0)),

HoldTime = SUM(isnull(HoldTime,0))

FROM Call_Type_Interval

GROUP BY CallTypeID, DateTime

) CTI

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

LEFT OUTER JOIN (SELECT

SkillGroupSkillTargetID,

DateTime= DateTime,

TalkInTime = SUM(ISNULL(TalkInTime,0))

FROM Agent_Skill_Group_Interval

WHERE  DateTime >= :startDate

and DateTime <= :endDate

GROUP BY SkillGroupSkillTargetID, DateTime

) ASGI

ON ASGI.SkillGroupSkillTargetID = CTSGG.SkillGroupSkillTargetID AND CTI.DateTime = CTSG.DateTime

WHERE  CTSG.CallTypeID IN (:CallTypeID)

AND CTSG.DateTime >= :startDate

AND CTSG.DateTime <= :endDate

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSGG.DateTime, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

Hey Robert,

The reason you are seeing the Skill Group sum error show up again is you are grouping by SkillGroupSkillTargetID in the inner queries. By doing this you prevent the code from summing up the data from all skill groups into one row which creates issues with the join. What you can do instead is join Agent_Skill_Group_Interval inside the CTSGG table on SkillGroupSkillTargetID. It should look something like this:

(SELECT

DateTime= DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

talkTime= sum(isnull(TalkTime,0)),

holdTime= sum(isnull(HoldTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),

TalkInTime = sum(isnull(ASGI.TalkInTime,0))

FROM Call_Type_SG_Interval

LEFT OUTER JOIN (SELECT

SkillGroupSkillTargetID,

DateTime= DateTime,

TalkInTime = SUM(ISNULL(TalkInTime,0))

FROM Agent_Skill_Group_Interval

WHERE  DateTime >= :startDate

and DateTime <= :endDate

GROUP BY SkillGroupSkillTargetID, DateTime

) ASGI

ON ASGI.SkillGroupSkillTargetID = CTSGG.SkillGroupSkillTargetID AND CTI.DateTime = CTSG.DateTime

WHERE CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, CallTypeID

) CTSGG

This query also allows your to get rid of that CTSG table above since this table doesn't need to group by SkillGroupSkillTargetID anymore. Give that a try and let me know how it goes.

- Richard

Hey Richard,

Thanks for helping me out - I really appreciate it!

I tried your query but unfortunately it gave me the error below.

"Create the parameters or correct the query syntax and recreate the parameters. Incorrect syntax near 'CTSGG'."

Unfortunately I´m not too familiar with how you created this query so I have no idea how to fix it. I did some tries and ended up with the query below, but it gave me too many CallsOffered again.

SELECT

CTSG.DateTime,

SUM(ISNULL(CTI.CallsOffered,0)) as CallsOffered,

sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)) as ASA

From (Select

DateTime= DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),

SkillGroupSkillTargetID

FROM Call_Type_SG_Interval

WHERE CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, CallTypeID, SkillGroupSkillTargetID) CTSG

LEFT OUTER JOIN (SELECT

SkillGroupSkillTargetID,

DateTime= DateTime,

TalkInTime = SUM(ISNULL(TalkInTime,0))

FROM Agent_Skill_Group_Interval

WHERE  DateTime >= :startDate

and DateTime <= :endDate

GROUP BY SkillGroupSkillTargetID, DateTime

) ASGI

ON ASGI.SkillGroupSkillTargetID = CTSG.SkillGroupSkillTargetID AND ASGI.DateTime = CTSG.DateTime

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID,

CallsOffered=SUM(ISNULL(CallsOffered,0)),

TalkTime = SUM(isnull(TalkTime,0)),

HoldTime = SUM(isnull(HoldTime,0))

FROM Call_Type_Interval

GROUP BY CallTypeID, DateTime

) CTI

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

WHERE CTSG.CallTypeID IN (:CallTypeID)

and CTSG.DateTime >= :startDate

and CTSG.DateTime <= :endDate

GROUP BY CTSG.DateTime

Hey Robert,

There were actually a couple of sloppy errors in the code I gave you. I had copy pasted without modifying a couple things so it would work. Try this out. I tested it and this does work. Should solve your problem.

SELECT

CT.EnterpriseName as EnterpriseName,

CTSG.DateTime as DateTime,

SUM(CTI.CallsOffered) as CallsOffered,

SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,

SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,

SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered

FROM

(SELECT

DateTime= Call_Type_SG_Interval.DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

talkTime= sum(isnull(TalkTime,0)),

holdTime= sum(isnull(HoldTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),

TalkInTime = sum(isnull(ASGI.TalkInTime,0))

FROM Call_Type_SG_Interval

LEFT OUTER JOIN (SELECT

SkillGroupSkillTargetID,

DateTime= DateTime,

TalkInTime = SUM(ISNULL(TalkInTime,0))

FROM Agent_Skill_Group_Interval

WHERE  DateTime >= :startDate

and DateTime <= :endDate

GROUP BY SkillGroupSkillTargetID, DateTime

) ASGI

ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime

WHERE CallTypeID IN (:CallTypeID)

and Call_Type_SG_Interval.DateTime >= :startDate

and Call_Type_SG_Interval.DateTime <= :endDate

GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID

) CTSG

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID,

CallsOffered=SUM(ISNULL(CallsOffered,0)),

TalkTime = SUM(isnull(TalkTime,0)),

HoldTime = SUM(isnull(HoldTime,0))

FROM Call_Type_Interval

GROUP BY CallTypeID, DateTime

) CTI

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

WHERE  CTSG.CallTypeID IN (:CallTypeID)

AND CTSG.DateTime >= :startDate

AND CTSG.DateTime <= :endDate

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

Thanks man, it worked! Got the correct number of CallsOffered now

It doesnt seem like its working to add any columns from ASGI table to the top though, getting errors like this:

Create the parameters or correct the query syntax and recreate the parameters. The multi-part identifier "ASGI.BusyOtherTime" could not be bound.

Here´s my current query (will do the ACW, AHT, ATT calculations later)

SELECT

CT.EnterpriseName as EnterpriseName,

CTSG.DateTime as DateTime,

SUM(CTI.CallsOffered) as CallsOffered,

SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,

SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,

SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(CTSG.CallsAnswered, 0)) as ACW,

SUM(ISNULL(CTSG.CallsAnswered, 0)) as ATT,

SUM(ISNULL(CTSG.CallsAnswered, 0)) as AHT,

SUM(ISNULL(ASGI.BusyOtherTime,0)) as BusyOtherTime,

SUM(ISNULL(ASGI.WorkReadyTime ,0)) as WorkReadyTime ,

SUM(ISNULL(ASGI.WorkNotReadyTime ,0)) as WorkNotReadyTime ,

SUM(ISNULL(ASGI.ReservedStateTime,0)) as ReservedStateTime,

--SUM(ISNULL(AI.AvailTime,0)) as AvailTime, --will add later

SUM(ISNULL(ASGI.TalkInTime,0) ) as TalkInTime,

SUM(ISNULL(ASGI.HoldTime,0)) as HoldTime

FROM

(SELECT

DateTime= Call_Type_SG_Interval.DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

talkTime= sum(isnull(TalkTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0))

FROM Call_Type_SG_Interval

LEFT OUTER JOIN (SELECT

SkillGroupSkillTargetID,

DateTime= DateTime,

WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),

WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),

TalkInTime = SUM(ISNULL(TalkInTime,0)),

TalkOutTime= SUM(ISNULL(TalkOutTime,0)),

AgentOutCalls= SUM(ISNULL(AgentOutCalls,0)),

AvailTime= SUM(ISNULL(AvailTime,0)),

HoldTime = SUM(ISNULL(HoldTime,0)),

BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),

ReservedStateTime = SUM(ISNULL(ReservedStateTime,0))

FROM Agent_Skill_Group_Interval

WHERE  DateTime >= :startDate

and DateTime <= :endDate

GROUP BY SkillGroupSkillTargetID, DateTime

) ASGI

ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime

WHERE CallTypeID IN (:CallTypeID)

and Call_Type_SG_Interval.DateTime >= :startDate

and Call_Type_SG_Interval.DateTime <= :endDate

GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID

) CTSG

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID,

CallsOffered=SUM(ISNULL(CallsOffered,0)),

TalkTime = SUM(isnull(TalkTime,0)),

HoldTime = SUM(isnull(HoldTime,0))

FROM Call_Type_Interval

GROUP BY CallTypeID, DateTime

) CTI

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

WHERE  CTSG.CallTypeID IN (:CallTypeID)

AND CTSG.DateTime >= :startDate

AND CTSG.DateTime <= :endDate

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

Hey Robert,

Because of the way this query is set up, adding fields from the ASGI table is a little trickier. Currently, you have a number of nested tables that you create inside the query and you are nesting the ASGI table inside the already nested CTSG table. So to add a field to the overall query, you need to add it first to the ASGI nested table, then call and sum that field in the CTSG table, then you can call that field in the top query as CTSG and the field name. Here's what the query will look like when you do that (and I've bolded the changes so you can see what I did and add more fields yourself later on). Let me know if this doesn't make sense.

SELECT

CT.EnterpriseName as EnterpriseName,

CTSG.DateTime as DateTime,

SUM(CTI.CallsOffered) as CallsOffered,

SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,

SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,

SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(CTSG.CallsAnswered, 0)) as ACW,

SUM(ISNULL(CTSG.CallsAnswered, 0)) as ATT,

SUM(ISNULL(CTSG.CallsAnswered, 0)) as AHT,

SUM(ISNULL(CTSG.BusyOtherTime,0)) as BusyOtherTime,

SUM(ISNULL(CTSG.WorkReadyTime ,0)) as WorkReadyTime ,

SUM(ISNULL(CTSG.WorkNotReadyTime ,0)) as WorkNotReadyTime ,

SUM(ISNULL(CTSG.ReservedStateTime,0)) as ReservedStateTime,

--SUM(ISNULL(AI.AvailTime,0)) as AvailTime, --will add later

SUM(ISNULL(CTSG.TalkInTime,0) ) as TalkInTime,

SUM(ISNULL(CTSG.HoldTime,0)) as HoldTime

FROM

(SELECT

DateTime= Call_Type_SG_Interval.DateTime,

CallTypeID= CallTypeID,

AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),

CallsAnswered= sum(isnull( CallsAnswered,0)),

callsHandled= sum(isnull(CallsHandled,0)),

handleTime= sum(isnull(HandleTime,0)),

talkTime= sum(isnull(TalkTime,0)),

callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),

callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),

overflowOut=sum(isnull(OverflowOut,0)),

att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),

aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),

asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0))

WorkNotReadyTime = SUM(ISNULL(ASGI.WorkNotReadyTime,0)),

WorkReadyTime = SUM(ISNULL(ASGI.WorkReadyTime,0)),

TalkInTime = SUM(ISNULL(ASGI.TalkInTime,0)),

TalkOutTime= SUM(ISNULL(ASGI.TalkOutTime,0)),

AgentOutCalls= SUM(ISNULL(ASGI.AgentOutCalls,0)),

AvailTime= SUM(ISNULL(ASGI.AvailTime,0)),

HoldTime = SUM(ISNULL(ASGI.HoldTime,0)),

BusyOtherTime= SUM(ISNULL(ASGI.BusyOtherTime,0)),

ReservedStateTime = SUM(ISNULL(ASGI.ReservedStateTime,0))

FROM Call_Type_SG_Interval

LEFT OUTER JOIN (SELECT

SkillGroupSkillTargetID,

DateTime= DateTime,

WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),

WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),

TalkInTime = SUM(ISNULL(TalkInTime,0)),

TalkOutTime= SUM(ISNULL(TalkOutTime,0)),

AgentOutCalls= SUM(ISNULL(AgentOutCalls,0)),

AvailTime= SUM(ISNULL(AvailTime,0)),

HoldTime = SUM(ISNULL(HoldTime,0)),

BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),

ReservedStateTime = SUM(ISNULL(ReservedStateTime,0))

FROM Agent_Skill_Group_Interval

WHERE  DateTime >= :startDate

and DateTime <= :endDate

GROUP BY SkillGroupSkillTargetID, DateTime

) ASGI

ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime

WHERE CallTypeID IN (:CallTypeID)

and Call_Type_SG_Interval.DateTime >= :startDate

and Call_Type_SG_Interval.DateTime <= :endDate

GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID

) CTSG

LEFT JOIN Call_Type CT (nolock)

ON CT.CallTypeID = CTSG.CallTypeID

LEFT OUTER JOIN (SELECT

DateTime= DateTime,

CallTypeID,

CallsOffered=SUM(ISNULL(CallsOffered,0)),

TalkTime = SUM(isnull(TalkTime,0)),

HoldTime = SUM(isnull(HoldTime,0))

FROM Call_Type_Interval

GROUP BY CallTypeID, DateTime

) CTI

ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime

WHERE  CTSG.CallTypeID IN (:CallTypeID)

AND CTSG.DateTime >= :startDate

AND CTSG.DateTime <= :endDate

GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime, CTSG.DateTime

ORDER BY CT.EnterpriseName, CTSG.DateTime

Create
Recognize Your Peers
Content for Community-Ad