Had a request from a customer for a report showing the number of Remedy incidents transferred from his managed group to other groups during a given date range.
I figured out a pseudo lead/lag query for SQL Server 2008. This creates an output that aligns the current row with the previous row. This allows you to see FROM which group an incident was transferred TO.
--- Just for testing; these will be the parameters for the SSRS report.
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '1/1/2015'
set @EndDate = '1/15/2015'
-- Get Incidents from Assignment Log where ET did something and put them into a temporary table.
select distinct(incident_number)
into #tempincidents
from View_HPD_Help_Desk_Assignment_Log
where assigned_group = 'Enterprise Telecomm'
and submit_date between @StartDate and @EndDate
order by incident_number
Here’s the magic, query the Assignment Log again using the Incident numbers from above and assign a row number.
select
incident_number
, Assignee
, assigned_group
, submit_date
, ROW_NUMBER() over(order by incident_number, submit_date) Serial
into #tempxfersA
from View_HPD_Help_Desk_Assignment_Log
where Incident_Number in (select * from #tempincidents)
select a.Incident_Number
-- 'Previous' Assigned Groups
, b.Assigned_Group as AGroup
, b.Assignee as AssigneeB
, b.Serial as SerialB
-- 'Current' Assigned Groups
, a.Assigned_Group as BGroup
, A.Assignee
, a.Serial
into #tempxfersB
from #tempxfersA a
-- Join the table to itself matching the Row number minus 1 and Incident number for quality.
left join #tempxfersA b on a.Serial - 1 = b.Serial and a.Incident_Number = b.Incident_Number
order by a.Incident_Number, a.submit_date
Your query output should look like
Incident Number, ‘First Assigned Group’, Assignee, Row # from Table ‘A’, ‘Second Assigned Group’, Assignee, Row # from Table ‘B’
Lets count them up… This returns the overall counts for Enterprise Telecomm incident transfers to another group.
select
--- Count where Enterprise Telecomm transferred an incident to another group.
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
--- While we are at it, count where a member of Enterprise Telecom transfered (re-assigned) an incident to someone else in the Telecomm group.
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
from #tempxfersB
And now let’s tally up transfers by the Assignee.
select
AssigneeB,
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
from #tempxfersB
group by AssigneeB
Caveats: 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.
