Query help needed 1

[jahlmer]

I have no relationships between the tables

Cases
CaseNo, AssignedTo

Activities
CaseNo, CreatedBy

There are many Activities for each Case (calls in and calls out) and both tables have many many more fields than I have discribed above. I need to identify a percentage for each owner (grouped by owner) to measure what ratio of a person's Cases contain no Activities created by someone other than the person assigned to the Case, compared to ones that do. End Result I need is:

Joe 85%
Don 50%
Ron 95% (of his cases had activities that were created by him and nobody else)

Pretty sure I need to Group by AssignedTo, and an Inner Join, but just can't fit it together properly to make it happen.

This query is going to report a single person resolution % in a call center environment.

Thanks in Advance

 

[JonFer]

There are a couple of ways to do this. This involves two queries. You can do it in one query with a NOT EXISTS condition.

qrySinglePerson

Select CaseNo, Min(CreatedBy) as CreatedBy
From Activities
Group By CaseNo
Having Min(CreatedBy)=Max(CreatedBy)

Final

Select AssignedTo, Count(c.CaseNo) as TotalCases, Count(s.CaseNo) as SoloHandled
From Cases c Left Join qrySinglePerson s On
     c.CaseNo=s.CaseNo And c.AssignedTo=s.CreatedBy
Group By AssignedTo

John

http://www.interautomateconsulting.com

 

[jahlmer]

JonFer, never got a chance to thank you for this. I've implemented it successfully. Having some issues getting the final ratio because of division by zero. Some individuals have no SoloHandled cases, but I'm looking for that answer elsewhere and may make a new Thread.

THANK YOU!!! Stars for you.

 

[jahlmer]

I really thought the above answer worked. I implemented it flawlessly, and was getting numbers. But, I'm realizing that it is not accounting for Cases that do not have Any activities under it... This is making cases without activities be considered as Not SoloHandled. Any Ideas?

Thanks again.

 

[JonFer]

The Left Join in the 2nd query above should bring in all Cases whether or not they have activities. If you have added a WHERE clause using a field from "qrySinglePerson" though, it will remove the Cases without Activities UNLESS you include "Or FieldName Is Null" as well.

If this isn't your situation, please post the SQL for your queries.

John

http://www.interautomateconsulting.com

 

[jahlmer]

Thanks for your quick response. That explains a lot about the Left Join...! I just couldn't believe how low the SPR % results are turning out to be, so pardon my dumb-founded and possibly premature last post. I need to go through the data manually once to verify this. If it turns out you're right (and you probably are!), I'll post again asap. Thank you so much for your insight.



qrySPR
SELECT c.Owner, Count(c.[SR #]) AS TotalCases, Count(s.[SR #]) AS SoloHandled, format((SOLOHANDLED/TOTALCASES),"00%") AS SPR
FROM tblSR AS c LEFT JOIN qrySinglePerson AS s ON (c.Owner=s.AssignedTo) AND (c.[SR #]=s.[SR #])
GROUP BY c.Owner;

qrySinglePerson
SELECT [SR #], Min([Assigned To]) AS AssignedTo
FROM tblActivity
WHERE ([Status] = "Done") and ([Type] = "Call - Inbound") or ([Type] = "Call - Outbound") or ([Type] = "Email - Outbound") or ([Type] = "Email - Inbound")
GROUP BY [SR #]
HAVING Min([Assigned To])=Max([Assigned To]);

 

[jahlmer]

Well I can confirm that it is not counting the cases that don't have activities. Does anyone have some suggestions?

Thank you

 

[JonFer]

Are you saying that a case without any activities should be considered as "solo-handled" by the owner?


John

http://www.interautomateconsulting.com

 

[jahlmer]

Yes! That is exactly it! It's been hard to track without looking at each case but after doing that, this is definately the underlying problem.

Thanks in advance, you've already helped me a lot and I appreciate your expertise

 

[JonFer]

One way is to make qrySinglePerson show all SRs with activities and let the top level query determine solo-handled:

SELECT [SR #], Min([Assigned To]) AS MinAssignedTo, Max([AssignedTo]) as MaxAssignedTo
FROM tblActivity
WHERE ([Status] = "Done") and ([Type] = "Call - Inbound") or ([Type] = "Call - Outbound")  or ([Type] = "Email - Outbound")  or ([Type] = "Email - Inbound")
GROUP BY [SR #] ;

Then the top level query would be:

SELECT c.Owner, Count(c.[SR #]) AS TotalCases, Sum(IIf(MaxAssignedTo=MinAssignedTo Or s.[SR #] Is Null,1,0)) AS SoloHandled, format((SOLOHANDLED/TOTALCASES),"00%") AS SPR
FROM tblSR AS c LEFT JOIN qrySinglePerson AS s ON (c.Owner=s.AssignedTo) AND (c.[SR #]=s.[SR #])
GROUP BY c.Owner;

The above is untested but should work.


John

http://www.interautomateconsulting.com