Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
224 lines (180 sloc) 8.53 KB
-- PQ Membership Query v4
SET NOCOUNT ON;
SET ANSI_WARNINGS ON;
DECLARE @query AS NVARCHAR(MAX),
@attribute_list AS NVARCHAR(MAX),
@atts_join AS NVARCHAR(MAX)
-- Construct a join statement string with Precision Queue step expressions in order to
-- join a list of PQ steps to a list of agents with associated attributes
SELECT @atts_join = (
SELECT DISTINCT ' OR ( pqs.PrecisionQueueStepID = ' + CAST(pqs2.PrecisionQueueStepID AS VARCHAR) + ' AND ' +
(
SELECT
CASE WHEN Precision_Queue_Term.TermRelation = 1 THEN ' AND '
WHEN Precision_Queue_Term.TermRelation = 2 THEN ' OR '
ELSE ''
END +
CASE WHEN Precision_Queue_Term.ParenCount = 1 THEN '('
ELSE ''
END +
'atts.' + QUOTENAME(Attribute.EnterpriseName) + ' ' +
CASE
When Precision_Queue_Term.AttributeRelation = 0 Then 'Unknown'
When Precision_Queue_Term.AttributeRelation = 1 Then '='
When Precision_Queue_Term.AttributeRelation = 2 Then '!='
When Precision_Queue_Term.AttributeRelation = 3 Then '<'
When Precision_Queue_Term.AttributeRelation = 4 Then '<='
When Precision_Queue_Term.AttributeRelation = 5 Then '>'
When Precision_Queue_Term.AttributeRelation = 6 Then '>='
-- the below are documented in API guide but I don't think actually used
-- if used they won't work at all presently
When Precision_Queue_Term.AttributeRelation = 7 Then 'between'
When Precision_Queue_Term.AttributeRelation = 8 Then 'member'
When Precision_Queue_Term.AttributeRelation = 9 Then 'agent has attribute'
When Precision_Queue_Term.AttributeRelation = 10 Then 'agent does not have attribute'
When Precision_Queue_Term.AttributeRelation = 11 Then 'not member'
END +
CASE
WHEN ISNUMERIC(Precision_Queue_Term.Value1) = 1 THEN Precision_Queue_Term.Value1
ELSE ' ''' + Precision_Queue_Term.Value1 + ''' '
END +
CASE
WHEN Precision_Queue_Term.ParenCount = -1 THEN ')'
ELSE ''
END
AS [text()]
FROM Precision_Queue, Precision_Queue_Step, Precision_Queue_Term, Attribute
WHERE
Precision_Queue.PrecisionQueueID = Precision_Queue_Step.PrecisionQueueID and
Precision_Queue_Step.PrecisionQueueStepID = Precision_Queue_Term.PrecisionQueueStepID and
Precision_Queue_Step.PrecisionQueueStepID = pqs2.PrecisionQueueStepID and
Attribute.AttributeID = Precision_Queue_Term.AttributeID
-- CUIC param to filter by PQS, 1 of 2
-- Edit or remove this if running directly in SQL Studio
AND Precision_Queue.PrecisionQueueID IN (:pqs)
ORDER BY Precision_Queue.PrecisionQueueID,
Precision_Queue_Step.PrecisionQueueStepID,
Precision_Queue_Step.StepOrder,
Precision_Queue_Term.TermOrder
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
+ ' )' AS [text()]
FROM Precision_Queue , Precision_Queue_Step pqs2,Precision_Queue_Term,Attribute
WHERE
Precision_Queue.PrecisionQueueID = pqs2.PrecisionQueueID and
pqs2.PrecisionQueueID = Precision_Queue_Term.PrecisionQueueID and
pqs2.PrecisionQueueStepID = Precision_Queue_Term.PrecisionQueueStepID and
Attribute.AttributeID = Precision_Queue_Term.AttributeID
FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)');
-- Build comma-delimited string list of attributes to
-- use when building a pivot table with agent attributes
SELECT @attribute_list = STUFF((
SELECT ',' + QUOTENAME(att.EnterpriseName)
FROM Attribute att
GROUP BY att.EnterpriseName
ORDER BY att.EnterpriseName
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,'');
-- Construct the final query
set @query = N'INSERT INTO #temp_pq_table SELECT AgentName, AgentSkillTargetID, pq.PrecisionQueueID, pq.EnterpriseName QueueName '
+ N', MIN(pqs.StepOrder) FirstStep ' +
N' FROM
(
SELECT a.EnterpriseName AgentName, a.SkillTargetID AgentSkillTargetID, aatt.AttributeValue, att.EnterpriseName AttributeName
FROM Agent a
INNER JOIN Agent_Attribute aatt
ON a.SkillTargetID = aatt.SkillTargetID
INNER JOIN Attribute att
ON att.AttributeID = aatt.AttributeID
) x
pivot
(
max(AttributeValue)
for AttributeName in (' + @attribute_list + N')
) atts INNER JOIN Precision_Queue_Step pqs ON 1=0 ' + @atts_join +
N' INNER JOIN Precision_Queue pq ON pqs.PrecisionQueueID = pq.PrecisionQueueID
GROUP BY AgentName, AgentSkillTargetID, pq.PrecisionQueueID, pq.EnterpriseName ';
--select @query
IF OBJECT_ID('tempdb.dbo.#temp_pq_table', 'U') IS NOT NULL
DROP TABLE #temp_pq_table;
CREATE TABLE #temp_pq_table(AgentName nvarchar(300), AgentSkillTargetID int, PrecisionQueueID int, QueueName nvarchar(300), FirstStep int );
-- Run the main query, which dumps the results in the temp table
exec sp_executesql @query
-- Query the results from the temp table and do our final joins
SELECT pqm.*,
CASE(art.AgentState)
WHEN 1 THEN 'Logged On'
WHEN 2 THEN 'Not Ready'
WHEN 3 THEN 'Ready'
WHEN 4 THEN 'Talking'
WHEN 5 THEN 'Work Not Ready'
WHEN 6 THEN 'Work Ready'
WHEN 7 THEN 'Busy Other'
WHEN 8 THEN 'Reserved'
ELSE 'Logged Out'
END AgentState,
art.ReasonCode,
art.AgentState AgentStateCode,
'Step ' + CAST(FirstStep AS VARCHAR) FirstStepLabel,
pqs.PrecisionQueueStepID,
rules.PQ_Rules PrecisionQueueRules
FROM #temp_pq_table pqm
INNER JOIN Precision_Queue pq ON pq.PrecisionQueueID = pqm.PrecisionQueueID
INNER JOIN Precision_Queue_Step pqs
ON pqs.PrecisionQueueID = pq.PrecisionQueueID
AND pqm.FirstStep = pqs.StepOrder
INNER JOIN Agent a ON pqm.AgentSkillTargetID = a.SkillTargetID
LEFT JOIN Agent_Real_Time art on a.SkillTargetID = art.SkillTargetID
-- join in the rule string for this precision queue step
INNER JOIN (
SELECT DISTINCT Precision_Queue.PrecisionQueueID, pqs2.PrecisionQueueStepID, pqs2.StepOrder Step,
'' + (SELECT
CASE WHEN Precision_Queue_Term.TermRelation = 1 THEN ' AND '
WHEN Precision_Queue_Term.TermRelation = 2 THEN ' OR '
ELSE '' END +
CASE WHEN Precision_Queue_Term.ParenCount = 1 THEN '[ '
ELSE '' END +
'(' + Attribute.EnterpriseName + ' ' +
CASE
When Precision_Queue_Term.AttributeRelation = 0 Then 'Unknown'
When Precision_Queue_Term.AttributeRelation = 1 Then '=='
When Precision_Queue_Term.AttributeRelation = 2 Then '!='
When Precision_Queue_Term.AttributeRelation = 3 Then '<'
When Precision_Queue_Term.AttributeRelation = 4 Then '<='
When Precision_Queue_Term.AttributeRelation = 5 Then '>'
When Precision_Queue_Term.AttributeRelation = 6 Then '>='
When Precision_Queue_Term.AttributeRelation = 7 Then 'between'
When Precision_Queue_Term.AttributeRelation = 8 Then 'member'
When Precision_Queue_Term.AttributeRelation = 9 Then 'agent has attribute'
When Precision_Queue_Term.AttributeRelation = 10 Then 'agent does not have attribute'
When Precision_Queue_Term.AttributeRelation = 11 Then 'not member'
END +
' ' + Precision_Queue_Term.Value1 + ')' +
CASE
WHEN Precision_Queue_Term.ParenCount = -1 THEN ' ]'
ELSE '' END
AS [text()]
FROM Precision_Queue, Precision_Queue_Step,Precision_Queue_Term,Attribute
WHERE
Precision_Queue.PrecisionQueueID = Precision_Queue_Step.PrecisionQueueID and
Precision_Queue_Step.PrecisionQueueID = Precision_Queue_Term.PrecisionQueueID and
Precision_Queue_Step.PrecisionQueueStepID = Precision_Queue_Term.PrecisionQueueStepID and
Precision_Queue_Step.PrecisionQueueStepID = pqs2.PrecisionQueueStepID and
Attribute.AttributeID = Precision_Queue_Term.AttributeID
ORDER BY Precision_Queue.PrecisionQueueID,
Precision_Queue_Step.PrecisionQueueStepID,
Precision_Queue_Step.StepOrder,
Precision_Queue_Term.TermOrder
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
AS [PQ_Rules]
FROM Precision_Queue , Precision_Queue_Step pqs2,Precision_Queue_Term,Attribute
WHERE
Precision_Queue.PrecisionQueueID = pqs2.PrecisionQueueID and
pqs2.PrecisionQueueID = Precision_Queue_Term.PrecisionQueueID and
pqs2.PrecisionQueueStepID = Precision_Queue_Term.PrecisionQueueStepID and
Attribute.AttributeID = Precision_Queue_Term.AttributeID
) rules ON rules.PrecisionQueueStepID = pqs.PrecisionQueueStepID
-- end join on precision queue rules string
-- CUIC param to filter by PQS, 2 of 2
-- Edit or remove this if running directly in SQL Studio
WHERE pqm.PrecisionQueueID in (:pqs)
DROP TABLE #temp_pq_table