--- Just for testing; these will be the parameters for the SSRS report.
\ndeclare @StartDate datetime
\ndeclare @EndDate datetime
\nset @StartDate = '1\/1\/2015'
\nset @EndDate = '1\/15\/2015'<\/p>\n-- Get Incidents from Assignment Log where ET did something and put them into a temporary table.
\nselect distinct(incident_number)
\ninto #tempincidents
\nfrom View_HPD_Help_Desk_Assignment_Log
\nwhere assigned_group = 'Enterprise Telecomm'
\nand submit_date between @StartDate and @EndDate
\norder by incident_number<\/code><\/p>\n
Here’s the magic, query the Assignment Log again using the Incident numbers from above and assign a row number.
\n
\nselect
\n incident_number
\n, Assignee
\n, assigned_group
\n, submit_date
\n, ROW_NUMBER() over(order by incident_number, submit_date) Serial
\ninto #tempxfersA
\nfrom View_HPD_Help_Desk_Assignment_Log
\nwhere Incident_Number in (select * from #tempincidents) <\/p>\nselect a.Incident_Number
\n-- 'Previous' Assigned Groups
\n, b.Assigned_Group as AGroup
\n, b.Assignee as AssigneeB
\n, b.Serial as SerialB
\n-- 'Current' Assigned Groups
\n, a.Assigned_Group as BGroup
\n, A.Assignee
\n, a.Serial
\ninto #tempxfersB
\nfrom #tempxfersA a
\n -- Join the table to itself matching the Row number minus 1 and Incident number for quality.
\n\tleft join #tempxfersA b on a.Serial - 1 = b.Serial and a.Incident_Number = b.Incident_Number
\n\torder by a.Incident_Number, a.submit_date<\/code><\/p>\n
Your query output should look like
\nIncident Number, ‘First Assigned Group’, Assignee, Row # from Table ‘A’, ‘Second Assigned Group’, Assignee, Row # from Table ‘B’<\/p>\n
Lets count them up… This returns the overall counts for Enterprise Telecomm incident transfers to another group.
\nselect
\n--- Count where Enterprise Telecomm transferred an incident to another group.
\nCOUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
\n--- While we are at it, count where a member of Enterprise Telecom transfered (re-assigned) an incident to someone else in the Telecomm group.
\nCOUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
\nfrom #tempxfersB
\n<\/code>
\nAnd now let’s tally up transfers by the Assignee.
\nselect
\nAssigneeB,
\nCOUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
\nCOUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
\nfrom #tempxfersB
\ngroup by AssigneeB
\n<\/code><\/p>\nCaveats: This just shows transfers to other groups and not necessarily for resolution. In my case, transfers can happen multiple times between groups on one incident without resolution.<\/p>\n
![\"TransferTicketCounts\"](\"http:\/\/54.159.73.21\/wp-content\/uploads\/2015\/01\/Capture.jpg\")
<\/a><\/p>\n