| Author |
Message |
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
   Topic: Assignments resolved by one individual
Posted: 10 Oct 2007 at 7:13am |
I have to find out how many tickets were resolved by only one certain individual. In other words, a call can have many assignments to many people(many people can contribute to the resolution). I need to find calls where this individual was the only assignee on the resolved call. HELP !!!!
|
 IP Logged |
|
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 10 Oct 2007 at 10:19am |
For those on the forum to help, it would be good to see some description of the data.
Generally, you should be able to Select the individual and then left join to the assignments table. However from your limited post I am not sure that is issue.
If you can give us more detail, I am certain someone here can help.
Regards,
John W.
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 10 Oct 2007 at 11:12am |
Sorry. I'm not sure how to better explain.
A trouble ticket is created and assigned to specific technician group/s. One individual will pick up an assignment and resolve it. A lot of times others have assignments on the ticket as well (maybe because the first technician had to reassign it to someone else). I need to report on the number of tickets that one specific individual resolved all by himself. There were no other assignments on the call for anyone else in any group.
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 10 Oct 2007 at 11:33am |
Lets step through it.
Do you havemore than one table in the database? If so is there one for the persons and another for the assignments? What are their names?
What are the fields that relate the two tables together. For example does the what identifies the person as having a particular assignment? Is there a Person Id that show in the Assignment table. If you have a many to many situation is there a linking table and how is it structured?
Have you written any queries so far even if they have not worked.? Seeingwhat has failed is helpful.
Having this kind of information is necessary for those on forum to help. Otherwise we cant give you enough detail to help.
Regards,
John W.
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 10 Oct 2007 at 11:56am |
I'm so sorry and thank you for tolerating me and tryint go help.
I'm knew at this forum.
I have a Call Log table (call info) and an Assignment table (assignment info) that I am using for this report. The Assignees are included in the Assignment table.
There is a CallID that links the two tables together. (CallLog.CallID --> Asgnmnt.CallID). I am using a Left outer join.
Each assignee has an assignee id within the Assignment table.
I have not tried anything yet, as I only know how to show all of the callid's that this assignee had at least 1 assignment on. I need to show the calls he has been the only Assignee on.
Call Log Table Assignment Table
Call Id Assignee
Call Description When individual assignment on call is resolved
Call Status
When call is Closed
Does this help ?
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 11 Oct 2007 at 7:35am |
Many to Many relationships are difficult.
Here are my thoughts, which may lead you to a better solution or a better solution from another forum member.
I would try something like this:
Create a CR database Command Object (in Database Expert) and enter this SQL Statement.
SELECT Asgnmnt.CallID, Assignee, DateResolved,CallLogTable.Description,CallLogTable.Status
FROM AssignmentTable
LEFT OUTER JOIN CallLogTable ON Asgnmnt.CallID = CallLogTable.CallID
WHERE CallLogTable.CallStatus = Closed
GROUP BY Assignee,Asngmnt.CallId
HAVING Distinct Count(Assignee) = 1
I cannot try this out so it may have errors. The logic I am trying to get to is have the select state pull all closed calls with the assignee's name, then to to group the calls by assignees, then to return those groups that there is only one assignee that handled and closed the call.
It may need some tuning and others on the forum may be able to help clean up the logic.
Hope it helps,
Regards,
John W.
www.CustomReportWriters.net
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 11 Oct 2007 at 7:55am |
|
Thanks so much. I get an error on 'Distinct'
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 11 Oct 2007 at 8:29am |
Try changing it to -- Count Distinct(assignee)
John W.
|
|
IP Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
Posted: 11 Oct 2007 at 8:30am |
|
You count also try Count(assignee) = 1
|
|
IP Logged |
|
nmercure
Newbie
Joined: 25 Sep 2007
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 11 Oct 2007 at 11:11am |
This ended up working for me. I was able to get help onsite from someone much more experienced than I. Now I just have to figure out what this means........
SELECT DISTINCT
TOP 100 PERCENT COUNT(b.Assignee) AS Expr1, b.Assignee, a.CallID, a.ClosedDate, b.GroupName, a.RecvdDate, a.CallType, a.CallStatus, a.Priority,
a.RecvdTime, a.ClosedTime, b.DateAssign, b.DateAcknow, b.DateResolv, CAST(a.CallDesc AS varchar(250)) AS [Desc]
FROM dbo.CallLog a LEFT OUTER JOIN
dbo.Asgnmnt b ON a.CallID = b.CallID
GROUP BY a.CallID, a.ClosedDate, b.GroupName, a.RecvdDate, a.CallType, a.CallStatus, a.Priority, a.RecvdTime, a.ClosedTime, b.DateAssign, b.DateAcknow,
b.DateResolv, b.Assignee, CAST(a.CallDesc AS varchar(250))
HAVING (a.CallStatus = 'Closed') AND (COUNT(b.Assignee) = 1)
ORDER BY COUNT(b.Assignee)
Thanks so much for helping. I really appreciate it.
|
|
IP Logged |
|
|
|
