Yup, another one. I keep finding myself writing these god-aweful stored procedures for reporting.
The run down:
This is a quality assurance reporting tool.
managers/qa people listen to recordings, and fill in how the agent did per question...
EE = exceedes expectations
ME = meets expectations
RI = remidial something or other
NA = not applicable.
EE and ME are both worth 1 point. RI is worth 0.
NA's are not counted towards the score, nor the total.
csr = customer service rep.
Q1-Q8 are for the etiquette portion of the review, and Q9-Q18 are for the accuracy, and thus need to be calculated seperately.
and don't laugh at the pony club..... this just happens to be the program that the procedure was written for.
ok, lets let the code do the talking....
and that makes a pretty little table
The run down:
This is a quality assurance reporting tool.
managers/qa people listen to recordings, and fill in how the agent did per question...
EE = exceedes expectations
ME = meets expectations
RI = remidial something or other
NA = not applicable.
EE and ME are both worth 1 point. RI is worth 0.
NA's are not counted towards the score, nor the total.
csr = customer service rep.
Q1-Q8 are for the etiquette portion of the review, and Q9-Q18 are for the accuracy, and thus need to be calculated seperately.
and don't laugh at the pony club..... this just happens to be the program that the procedure was written for.
ok, lets let the code do the talking....
Code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE PonyClub_Monitoring_Report
(
@startdate datetime,
@enddate datetime,
@csr varchar(100)
)
AS
begin
set nocount on
declare @queryString varchar(8000)
declare @queryString2 varchar(8000)
declare @queryString3 varchar(8000)
declare @queryString4 varchar(8000)
declare @queryString5 varchar(8000)
create table #report_temp
(
CSR varchar(30),
TotalCallsMonitored int,
Q1 int,
Q2 int,
Q3 int,
Q4 int,
Q5 int,
Q6 int,
Q7 int,
Q8 int,
ETIQUETTE_Points int,
ETIQUETTE_Total_NA int,
ETIQUETTE_SCORE varchar(20),
Q9 int,
Q10 int,
Q11 int,
Q12 int,
Q13 int,
Q14 int,
Q15 int,
Q16 int,
Q17 int,
Q18 int,
ACCURACY_Points int,
ACCURACY_Total_NA int,
ACCURACY_SCORE varchar(20)
)
----------- check for 0 records.... will "divide by 0" error out if not pre-tested.
declare @testquery varchar(8000)
declare @ucount float
if @csr = 'ALL'
begin
select @ucount = count(*) from tPonyClub_monitoring
where iacc_date between cast( @startdate + ' 00:00:01' AS varchar(30) ) AND cast( @enddate + ' 23:59:59' AS varchar(30) )
AND csr is NOT NULL
end
else
begin
select @ucount = count(*) from tPonyClub_monitoring
where iacc_date between cast( @startdate + ' 00:00:01' AS varchar(30) ) AND cast( @enddate + ' 23:59:59' AS varchar(30) )
AND csr = @csr
end
if (@ucount = 0)
begin
select '<h5>Error, no records for agent(s) selected in specified date range</h5>'
end
else
begin
--------------------------------main---------------------------------------
set @queryString = 'INSERT INTO #report_temp ' +
'select csr AS ''CSR'', count(*) AS ''TotalCallsMonitored'', count(case q1 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q1'', ' +
'count(case q2 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q2'', ' +
'count(case q3 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q3'', ' +
'count(case q4 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q4'', ' +
'count(case q5 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q5'', ' +
'count(case q6 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q6'', ' +
'count(case q7 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q7'', ' +
'count(case q8 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q8'', ' +
'count(case q1 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q2 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q3 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q4 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q5 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q6 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q7 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q8 when ''ME'' then 1 when ''EE'' then 1 else null end)
as ''ETIQUETTE_Points'', ' +
'count(case q1 when ''NA'' then 1 else null end) + count(case q2 when ''NA'' then 1 else null end) +
count(case q3 when ''NA'' then 1 else null end) + count(case q4 when ''NA'' then 1 else null end) +
count(case q5 when ''NA'' then 1 else null end) + count(case q6 when ''NA'' then 1 else null end) +
count(case q7 when ''NA'' then 1 else null end) + count(case q8 when ''NA'' then 1 else null end)
as ''ETIQUETTE_Total_NA'', ' +
--------------- start hell ------------------------------
'cast(round((cast(count(case q1 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q2 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q3 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q4 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q5 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q6 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q7 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q8 when null then null when ''NA'' then null when ''RI'' then null else 1 end) as float)
-
(count(case q1 when ''NA'' then 1 else null end) +
count(case q2 when ''NA'' then 1 else null end) +
count(case q3 when ''NA'' then 1 else null end) +
count(case q4 when ''NA'' then 1 else null end) +
count(case q5 when ''NA'' then 1 else null end) +
count(case q6 when ''NA'' then 1 else null end) +
count(case q7 when ''NA'' then 1 else null end) +
count(case q8 when ''NA'' then 1 else null end))
)
/
((count(case q1 when null then null else 1 end) +
count(case q2 when null then null else 1 end) +
count(case q3 when null then null else 1 end) +
count(case q4 when null then null else 1 end) +
count(case q5 when null then null else 1 end) +
count(case q6 when null then null else 1 end) +
count(case q7 when null then null else 1 end) +
count(case q8 when null then null else 1 end)
-
(count(case q1 when ''NA'' then 1 else null end) +
count(case q2 when ''NA'' then 1 else null end) +
count(case q3 when ''NA'' then 1 else null end) +
count(case q4 when ''NA'' then 1 else null end) +
count(case q5 when ''NA'' then 1 else null end) +
count(case q6 when ''NA'' then 1 else null end) +
count(case q7 when ''NA'' then 1 else null end) +
count(case q8 when ''NA'' then 1 else null end))
)
) * 100
,2) as varchar(20)) + ''%'' as ''ETIQUETTE_SCORE'', '
---------------------- end hell -----------------------------
set @querystring2 = 'count(case q9 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q9'', ' +
'count(case q10 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q10'', ' +
'count(case q11 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q11'', ' +
'count(case q12 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q12'', ' +
'count(case q13 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q13'', ' +
'count(case q14 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q14'', ' +
'count(case q15 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q15'', ' +
'count(case q16 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q16'', ' +
'count(case q17 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q17'', ' +
'count(case q18 when ''ME'' then 1 when ''EE'' then 1 else null end) as ''Q18'', ' +
'(count(case q9 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q10 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q11 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q12 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q13 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q14 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q15 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q16 when ''ME'' then 1 when ''EE'' then 1 else null end) +
count(case q17 when ''ME'' then 1 when ''EE'' then 1 else null end) + count(case q18 when ''ME'' then 1 when ''EE'' then 1 else null end))
as ''ACCURACY_Points'', ' +
'count(case q9 when ''NA'' then 1 else null end) + count(case q10 when ''NA'' then 1 else null end) +
count(case q11 when ''NA'' then 1 else null end) + count(case q12 when ''NA'' then 1 else null end) +
count(case q13 when ''NA'' then 1 else null end) + count(case q14 when ''NA'' then 1 else null end) +
count(case q15 when ''NA'' then 1 else null end) + count(case q16 when ''NA'' then 1 else null end) +
count(case q17 when ''NA'' then 1 else null end) + count(case q18 when ''NA'' then 1 else null end)
as ''ACCURACY_Total_NA'', ' +
--------------- start hell ------------------------------
'cast(round((cast(count(case q9 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q10 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q11 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q12 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q13 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q14 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q15 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q16 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q17 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q18 when null then null when ''NA'' then null when ''RI'' then null else 1 end) as float)
-
(count(case q9 when ''NA'' then 1 else null end) +
count(case q10 when ''NA'' then 1 else null end) +
count(case q11 when ''NA'' then 1 else null end) +
count(case q12 when ''NA'' then 1 else null end) +
count(case q13 when ''NA'' then 1 else null end) +
count(case q14 when ''NA'' then 1 else null end) +
count(case q15 when ''NA'' then 1 else null end) +
count(case q16 when ''NA'' then 1 else null end) +
count(case q17 when ''NA'' then 1 else null end) +
count(case q18 when ''NA'' then 1 else null end))
)
/
((count(case q9 when null then null else 1 end) +
count(case q10 when null then null else 1 end) +
count(case q11 when null then null else 1 end) +
count(case q12 when null then null else 1 end) +
count(case q13 when null then null else 1 end) +
count(case q14 when null then null else 1 end) +
count(case q15 when null then null else 1 end) +
count(case q16 when null then null else 1 end) +
count(case q17 when null then null else 1 end) +
count(case q18 when null then null else 1 end)
-
(count(case q9 when ''NA'' then 1 else null end) +
count(case q10 when ''NA'' then 1 else null end) +
count(case q11 when ''NA'' then 1 else null end) +
count(case q12 when ''NA'' then 1 else null end) +
count(case q13 when ''NA'' then 1 else null end) +
count(case q14 when ''NA'' then 1 else null end) +
count(case q15 when ''NA'' then 1 else null end) +
count(case q16 when ''NA'' then 1 else null end) +
count(case q17 when ''NA'' then 1 else null end) +
count(case q18 when ''NA'' then 1 else null end))
)
) * 100
,2) as varchar(20)) + ''%'' as ''ACCURACY_SCORE'' '
---------------------- end hell -----------------------------
set @querystring3 = 'from tPonyClub_monitoring ' +
'where iacc_date between ''' + cast( @startdate + ' 00:00:01' AS varchar(30) ) + ''' AND ''' + cast( @enddate + ' 23:59:59' AS varchar(30) ) + ''' '
if @csr = 'ALL'
begin
set @queryString3 = @queryString3 + ' and csr is NOT NULL '
end
else
begin
set @queryString3 = @queryString3 + ' and csr = ''' + @csr + ''' '
end
set @queryString3 = @queryString3 + ' GROUP BY csr'
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
--------------- TOTALS ------------------
set @querystring4 = ' INSERT INTO #report_temp ' +
'SELECT ''TOTALS'' as ''CSR'', count(*) as ''TotalCallsMonitored'', ' +
'count(case q1 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q2 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q3 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q4 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q5 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q6 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q7 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q8 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q1 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q2 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q3 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q4 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q5 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q6 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q7 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q8 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q1 when ''NA'' then 1 else null end) +
count(case q2 when ''NA'' then 1 else null end) +
count(case q3 when ''NA'' then 1 else null end) +
count(case q4 when ''NA'' then 1 else null end) +
count(case q5 when ''NA'' then 1 else null end) +
count(case q6 when ''NA'' then 1 else null end) +
count(case q7 when ''NA'' then 1 else null end) +
count(case q8 when ''NA'' then 1 else null end), ' +
--------------- start hell ------------------------------
'cast(round((cast(count(case q1 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q2 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q3 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q4 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q5 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q6 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q7 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q8 when null then null when ''NA'' then null when ''RI'' then null else 1 end) as float)
-
(count(case q1 when ''NA'' then 1 else null end) +
count(case q2 when ''NA'' then 1 else null end) +
count(case q3 when ''NA'' then 1 else null end) +
count(case q4 when ''NA'' then 1 else null end) +
count(case q5 when ''NA'' then 1 else null end) +
count(case q6 when ''NA'' then 1 else null end) +
count(case q7 when ''NA'' then 1 else null end) +
count(case q8 when ''NA'' then 1 else null end))
)
/
((count(case q1 when null then null else 1 end) +
count(case q2 when null then null else 1 end) +
count(case q3 when null then null else 1 end) +
count(case q4 when null then null else 1 end) +
count(case q5 when null then null else 1 end) +
count(case q6 when null then null else 1 end) +
count(case q7 when null then null else 1 end) +
count(case q8 when null then null else 1 end)
-
(count(case q1 when ''NA'' then 1 else null end) +
count(case q2 when ''NA'' then 1 else null end) +
count(case q3 when ''NA'' then 1 else null end) +
count(case q4 when ''NA'' then 1 else null end) +
count(case q5 when ''NA'' then 1 else null end) +
count(case q6 when ''NA'' then 1 else null end) +
count(case q7 when ''NA'' then 1 else null end) +
count(case q8 when ''NA'' then 1 else null end))
)
) * 100
,2) as varchar(20)) + ''%'' , ' +
---------------------- end hell -----------------------------
'count(case q9 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q10 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q11 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q12 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q13 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q14 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q15 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q16 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q17 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q18 when ''EE'' then 1 when ''ME'' then 1 else null end), ' +
'count(case q9 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q10 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q11 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q12 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q13 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q14 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q15 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q16 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q17 when ''EE'' then 1 when ''ME'' then 1 else null end) +
count(case q18 when ''EE'' then 1 when ''ME'' then 1 else null end) , ' +
'count(case q9 when ''NA'' then 1 else null end) +
count(case q10 when ''NA'' then 1 else null end) +
count(case q11 when ''NA'' then 1 else null end) +
count(case q12 when ''NA'' then 1 else null end) +
count(case q13 when ''NA'' then 1 else null end) +
count(case q14 when ''NA'' then 1 else null end) +
count(case q15 when ''NA'' then 1 else null end) +
count(case q16 when ''NA'' then 1 else null end) +
count(case q17 when ''NA'' then 1 else null end) +
count(case q18 when ''NA'' then 1 else null end), '
set @querystring5 =
--------------- start hell ------------------------------
'cast(round((cast(count(case q9 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q10 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q11 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q12 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q13 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q14 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q15 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q16 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q17 when null then null when ''NA'' then null when ''RI'' then null else 1 end) +
count(case q18 when null then null when ''NA'' then null when ''RI'' then null else 1 end) as float)
-
(count(case q9 when ''NA'' then 1 else null end) +
count(case q10 when ''NA'' then 1 else null end) +
count(case q11 when ''NA'' then 1 else null end) +
count(case q12 when ''NA'' then 1 else null end) +
count(case q13 when ''NA'' then 1 else null end) +
count(case q14 when ''NA'' then 1 else null end) +
count(case q15 when ''NA'' then 1 else null end) +
count(case q16 when ''NA'' then 1 else null end) +
count(case q17 when ''NA'' then 1 else null end) +
count(case q18 when ''NA'' then 1 else null end))
)
/
((count(case q9 when null then null else 1 end) +
count(case q10 when null then null else 1 end) +
count(case q11 when null then null else 1 end) +
count(case q12 when null then null else 1 end) +
count(case q13 when null then null else 1 end) +
count(case q14 when null then null else 1 end) +
count(case q15 when null then null else 1 end) +
count(case q16 when null then null else 1 end) +
count(case q17 when null then null else 1 end) +
count(case q18 when null then null else 1 end)
-
(count(case q9 when ''NA'' then 1 else null end) +
count(case q10 when ''NA'' then 1 else null end) +
count(case q11 when ''NA'' then 1 else null end) +
count(case q12 when ''NA'' then 1 else null end) +
count(case q13 when ''NA'' then 1 else null end) +
count(case q14 when ''NA'' then 1 else null end) +
count(case q15 when ''NA'' then 1 else null end) +
count(case q16 when ''NA'' then 1 else null end) +
count(case q17 when ''NA'' then 1 else null end) +
count(case q18 when ''NA'' then 1 else null end))
)
) * 100
,2) as varchar(20)) + ''%'' as ''ACCURACY_SCORE'' ' +
---------------------- end hell -----------------------------
'from tPonyClub_monitoring ' +
'where iacc_date between ''' + cast( @startdate + ' 00:00:01' AS varchar(30) ) + ''' AND ''' + cast( @enddate + ' 23:59:59' AS varchar(30) ) + ''' '
if @csr = 'ALL'
begin
set @queryString5 = @queryString5 + ' and csr is NOT NULL '
end
else
begin
set @queryString5 = @queryString5 + ' and csr = ''' + @csr + ''' '
end
exec(@querystring + @querystring2 + @querystring3 + @querystring4 + @querystring5)
select CSR,
TotalCallsMonitored AS 'Total Calls Monitored',
Q1,
Q2,
Q3,
Q4,
Q5,
Q6,
Q7,
Q8,
ETIQUETTE_Points AS 'Etiquette Points',
ETIQUETTE_Total_NA AS 'Etiquette N/A',
ETIQUETTE_SCORE AS 'Etiquette Score',
Q9,
Q10,
Q11,
Q12,
Q13,
Q14,
Q15,
Q16,
Q17,
Q18,
ACCURACY_Points AS 'Accuracy Points',
ACCURACY_Total_NA AS 'Accuracy N/A',
ACCURACY_SCORE AS 'Accuracy Score'
from #report_temp
end -- still need to drop, cuz table was created before testquery string
drop table #report_temp
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and that makes a pretty little table