god this thing sucked... lol
so much, that i decided to post it
first off, this report procedure for a front end interface I made for a QA monitoring thingee.
basically, they have EE, ME, RI, and NA options for 11 questions.
Excedes expectations, meets exp., (don't remember Ri... remdial something or other... ) and not applicable..
EE and ME are worth 1 point
RI is worth nothing
NA is worth nothing, but also is not counted at all towards the total number of questions, thus dropping the questions from 11 - the total NA's.
this was a real big pain in the ass. lol
basically, you have to count everything.. figure out how many there are, come up with a new adjusted total, then get the score-- all this in a derrived query string.
i was in parenthesis hell for a LONG time. lol
anyway, here's the SP i made.
the front end is a simple asp loop that calls the SP
thats how i spent noon to 430
so much, that i decided to post it
first off, this report procedure for a front end interface I made for a QA monitoring thingee.
basically, they have EE, ME, RI, and NA options for 11 questions.
Excedes expectations, meets exp., (don't remember Ri... remdial something or other... ) and not applicable..
EE and ME are worth 1 point
RI is worth nothing
NA is worth nothing, but also is not counted at all towards the total number of questions, thus dropping the questions from 11 - the total NA's.
this was a real big pain in the ass. lol
basically, you have to count everything.. figure out how many there are, come up with a new adjusted total, then get the score-- all this in a derrived query string.
i was in parenthesis hell for a LONG time. lol
anyway, here's the SP i made.
Code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------
----------------------------------------------------------------------
--------------------------DEBUG------------------------------------
-- sp_monitoring_report '05/12/2005', '05/20/2005', 'ALL'
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
alter PROCEDURE sp_monitoring_report
(
@startdate datetime,
@enddate datetime,
@agents varchar(50)
)
AS
begin
set nocount off
declare @queryString varchar(8000)
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
set @queryString = 'select iacc_date AS DATE, csr, tl, monitor, callid, calldate, ' +
'q1 AS Q1, ' +
'q2 AS Q2, ' +
'q3 AS Q3, ' +
'q4 AS Q4, ' +
'q5 AS Q5, ' +
'q6 AS Q6, ' +
'q7 AS Q7, ' +
'q8 AS Q8, ' +
'q9 AS Q9, ' +
'q10 AS Q10, ' +
'q11 AS Q11, ' +
'(case q1 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q2 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q3 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q4 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q5 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q6 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q7 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q8 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q9 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q10 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q11 when ''EE'' then 1 when ''ME'' then 1 else 0 end) AS ''Total Points'', ' +
'(case q1 when ''NA'' then 1 else 0 end) + (case q2 when ''NA'' then 1 else 0 end) + (case q3 when ''NA'' then 1 else 0 end) + (case q4 when ''NA'' then 1 else 0 end) + (case q5 when ''NA'' then 1 else 0 end) + (case q6 when ''NA'' then 1 else 0 end) + (case q7 when ''NA'' then 1 else 0 end) + (case q8 when ''NA'' then 1 else 0 end) + (case q9 when ''NA'' then 1 else 0 end) + (case q10 when ''NA'' then 1 else 0 end) + (case q11 when ''NA'' then 1 else 0 end) AS ''Total NA'', ' +
'cast(''11'' as int) AS ''Total Questions'', ' +
'cast(''11'' as int) - ((case q1 when ''NA'' then 1 else 0 end) + (case q2 when ''NA'' then 1 else 0 end) + (case q3 when ''NA'' then 1 else 0 end) + (case q4 when ''NA'' then 1 else 0 end) + (case q5 when ''NA'' then 1 else 0 end) + (case q6 when ''NA'' then 1 else 0 end) + (case q7 when ''NA'' then 1 else 0 end) + (case q8 when ''NA'' then 1 else 0 end) + (case q9 when ''NA'' then 1 else 0 end) + (case q10 when ''NA'' then 1 else 0 end) + (case q11 when ''NA'' then 1 else 0 end)) AS ''Adjusted Total Questions (Total Questions - Total NA)'', ' +
-- cast the whole shebang as a varchar
'cast(' +
-- round the whole thing
'Round( ' +
----------------------- -- total points
-- make it float
'cast(' +
'((case q1 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q2 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q3 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q4 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q5 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q6 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q7 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q8 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q9 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q10 when ''EE'' then 1 when ''ME'' then 1 else 0 end) + (case q11 when ''EE'' then 1 when ''ME'' then 1 else 0 end)) ' +
'AS float) ' +
-- end float
' / ' +
----------------------- -- adj total
-- make it float
'cast(' +
'((cast(''11'' as int) - (((case q1 when ''NA'' then 1 else 0 end) + (case q2 when ''NA'' then 1 else 0 end) + (case q3 when ''NA'' then 1 else 0 end) + (case q4 when ''NA'' then 1 else 0 end) + (case q5 when ''NA'' then 1 else 0 end) + (case q6 when ''NA'' then 1 else 0 end) + (case q7 when ''NA'' then 1 else 0 end) + (case q8 when ''NA'' then 1 else 0 end) + (case q9 when ''NA'' then 1 else 0 end) + (case q10 when ''NA'' then 1 else 0 end) + (case q11 when ''NA'' then 1 else 0 end))))) ' +
'AS float) ' +
-- end float
', 3) * 100 ' +
-- finish round
' AS varchar(10) ) + ''%'' ' +
-- finish varchar cast
'AS ''Total Points / Adjusted Total Questions'' ' +
'from tbl_Monitoring_sheet ' +
'where iacc_date between ''' + cast( @startdate + ' 00:00:01' AS varchar(30) ) + ''' AND ''' + cast( @enddate + ' 23:59:59' AS varchar(30) ) + ''' '
if @agents = 'ALL'
begin
set @queryString = @queryString + 'and csr is NOT NULL ORDER BY iacc_date, csr '
end
else
begin
set @queryString = @queryString + 'and csr = ''' + @agents + ''' ORDER BY iacc_date, csr '
end
exec(@queryString)
end
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
the front end is a simple asp loop that calls the SP
Code:
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll"-->
<%
connstr = "Provider=SQLOLEDB.1;UID=xxxxxxxxx;Password=xxxxxxxxxxx;Persist Security Info=False;Initial Catalog=xxxxxxxxxxxxxxxxxxxx;Data Source=xxxxxxxxxxxxxx"
'localize the vars
dim startdate,enddate,agents,report_type
startdate = request.form("startdate")
enddate = request.form("enddate")
agents = request.form("agents")
server.ScriptTimeout = 3600
set comm = Server.CreateObject ("ADODB.Command")
set rst = Server.CreateObject("ADODB.Recordset")
comm.CommandTimeout = 0
comm.ActiveConnection = connstr
comm.CommandText = "sp_monitoring_report"
comm.CommandType = adCmdStoredProc
comm.Parameters("@startdate") = startdate
comm.Parameters("@enddate") = enddate
comm.Parameters("@agents") = agents
set rst = comm.Execute
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Monitoring Report</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" media="screen" href="../../xxxxxxxxxxx/style.css" />
<style type="text/css" media="screen">
table { font-size: .7em; }
th { background: #f1f1f1; text-align: center; }
td { text-align: left; }
.subhead { background: #678; color: #fff; font-weight: bold; text-align: center;}
.divider { background: #333; }
#buttonrow { text-align: center; }
</style>
<style type="text/css" media="print">
body { font-family: times, roman, serif; font-size: 8pt; margin: 0pt; padding: 0pt; }
h1 { text-align: center; font-size: 12pt;}
</style>
<script type="text/javascript">
function gohome() {
var theform = document.getElementById("frmdefault");
theform.method="post";
theform.action="default.asp";
theform.submit();
}
</script>
</head>
<body>
<div id="wrapper">
<div id="header">
<h1>Monitoring Report</h1>
</div>
<div id="padder">
<div id="content" align="center">
<form name="frmdefault" id="frmdefault" action="" method="post">
<table border="1" bordercolor="#556677" cellspacing="0" cellpadding="2">
<tr>
<%
if not rst.EOF then
for x = 0 to rst.Fields.count - 1
if rst.Fields(x).Value = "SPACER" then
Response.Write "<th style=""background: #333;""></th>"
else
Response.Write "<th>" & trim(rst.Fields(x).Name) & "</th>" & vbcrlf
end if
next
end if
Response.Write "</tr>" & vbcrlf
if not rst.EOF then
do while not rst.eof
for x = 0 to rst.Fields.count - 1
if x=0 then
response.write "<tr>" & vbcrlf
end if
Response.Write "<td>" & trim(rst.Fields(x).Value) & "</td>" & vbcrlf
next
rst.MoveNext
Response.Write "</tr>" & vbcrlf
loop
else
Response.Write "<tr><td><h3>No records found based on date and agent entered.</h3></td></tr>"
end if
set rst = nothing
set comm = nothing
%>
</table>
</form>
<div id="buttonrow"><input type="button" onclick="gohome();" value="Report Menu" /></div>
</div>
</div>
</div>
</body>
</html>
thats how i spent noon to 430