I just wrote the most god-aweful program for work

We may earn a small commission from affiliate links and paid advertisements. Terms

posol

RETIRED
Code:
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll"-->
<%
	connstr = "Provider=SQLOLEDB.1;UID=xxxxxxxxxxxxxxxxx;Password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;Persist Security Info=False;Initial Catalog=xxxxxxxxxxxxxxxxxx;Data Source=xxxxxxxxxxxxxxxxxxxx"

	

	'localize the vars
	dim stringbuffer,startdate,enddate,agents,arrayagents,x,y,z,rst,comm

	startdate = request.form("startdate")
	enddate = request.form("enddate")
	agents = request.form("agents")



arrayagents = split(agents, ",")

for z=0 to ubound(arrayagents)
'	response.write Cstr(arrayagents(z)) & "<br>"
	stringbuffer = stringbuffer & "'" & trim(arrayagents(z)) & "',"
next

	stringbuffer = left(stringbuffer, len(stringbuffer) -1)

	'response.write stringbuffer



	'------------------------------------
	'and now, for the looooooooooooooooop
	'sp_scholastic_qa_report
	'------------------------------------

	server.ScriptTimeout = 3600
	set comm = Server.CreateObject ("ADODB.Command")
	set rst = Server.CreateObject("ADODB.Recordset")
	comm.CommandTimeout = 0
	comm.ActiveConnection = connstr
	comm.CommandText = "sp_scholastic_qa_report"
	comm.CommandType = adCmdStoredProc
	comm.Parameters("@startdate") = startdate
	comm.Parameters("@enddate") = enddate
	comm.Parameters("@agents") = stringbuffer
	
	set rst = comm.Execute
	'set comm = Nothing

	
 ' Build the table -->	

 	dim tablevar, alert2

 

 	y=0
 	fcount = rst.Fields.count-1

 	if not rst.EOF then    
 	'response.write rst.recordcount
  do until rst.eof
  tablevar = tablevar & "<tr>" & vbcrlf
  	for x = 0 to rst.Fields.count-1	

   if x < 14 then   	
   	tablevar = tablevar & "<td id=""row_"& y & "_col_" & x &""">" & rst.Fields(x).Value & "</td>" & vbcrlf 
   else
   	if x < 17 then
    tablevar = tablevar & "<td id=""row_"& y & "_col_" & x &""">0</td>" & vbcrlf
   	else
    'grab the na's
    tablevar = tablevar & "<td style=""visibility:hidden; width: 1px;"" id=""row_"& y & "_col_" & x &""">" & rst.Fields(x).Value & "</td>" & vbcrlf
   	end if
   end if  	
  	next
  	tablevar = tablevar & "</tr>" & vbcrlf
  	y=y+1
  	rst.MoveNext  
  loop	
 	
  
 	end if

  	


%>
<!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>Scholastic QA Report</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> 

<link rel="stylesheet" type="text/css" media="screen" href="../style.css" />

<style type="text/css" media="screen">
body { font-size: .7em; }
th { background: #f1f1f1; text-align: center; }
.subhead { background: #678; color: #fff; font-weight: bold; text-align: center;}
.divider { background: #333; }
</style>

<script type="text/javascript">

function initvalues() {

	var theform = document.getElementById("frmdefault");

	//ok, here, we need to assign all the values to the form after we do all kinds of crazy math.
	

	var fcount = theform.fcount.value;
	var ycount = theform.ycount.value;


	//ok, first the agent name. no need for math. loop it out for col0
	// also don't need math for col1, total calls

	for(var a=0; a < ycount; a++)
	{
 for(var f=0; f < 2; f++)
 {
 	eval('document.getElementById("datarow_'+a+'_col_'+f+'").innerHTML = document.getElementById("row_'+a+'_col_'+f+'").innerHTML');	
 }
	}


	// Q1 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=2; f < 3; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_14').innerHTML;
 	field = document.getElementById('row_'+a+'_col_2').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}

	// Q2 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=3; f < 4; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_15').innerHTML;
 	field = document.getElementById('row_'+a+'_col_3').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}

	// Q3 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=4; f < 5; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_16').innerHTML;
 	field = document.getElementById('row_'+a+'_col_4').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}

	// Q4 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=5; f < 6; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_17').innerHTML;
 	field = document.getElementById('row_'+a+'_col_5').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}

	
	// Q5 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=6; f < 7; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_18').innerHTML;
 	field = document.getElementById('row_'+a+'_col_6').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}



	// Q6 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=7; f < 8; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_19').innerHTML;
 	field = document.getElementById('row_'+a+'_col_7').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}

	// Q7 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=8; f < 9; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_20').innerHTML;
 	field = document.getElementById('row_'+a+'_col_8').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}


	// Q8 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=9; f < 10; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_21').innerHTML;
 	field = document.getElementById('row_'+a+'_col_9').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}


	// Q9 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=10; f < 11; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_22').innerHTML;
 	field = document.getElementById('row_'+a+'_col_10').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}



	// Q10 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=11; f < 12; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_23').innerHTML;
 	field = document.getElementById('row_'+a+'_col_11').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;	
 }
	}

	// Q11 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=12; f < 13; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_24').innerHTML;
 	field = document.getElementById('row_'+a+'_col_12').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}


	// Q12 - find NA's, subtract, then set to value percentage

	for(var a=0; a < ycount; a++)
	{
 for(var f=13; f < 14; f++)
 {
 	var na, field, rowcount

 	na = document.getElementById('row_'+a+'_col_25').innerHTML;
 	field = document.getElementById('row_'+a+'_col_13').innerHTML;
 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	totala = Number(rowcount) - Number(na);
 	totalb = (Number(field)/totala)*100;
 	totalc = formatNumber(totalb, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
 }
	}



//--------------------------------------------------------------------------------------------------------
	//ok,,,,,,,,,,,,,,, now, for the total's columns.... 

	// Q1 - Q4 Phone pres.

	for(var a=0; a < ycount; a++)
	{
 for(var f=14; f < 15; f++)
 {
 	var fielda, fieldb, fieldc, fieldd
 
 	fielda = document.getElementById('row_'+a+'_col_2').innerHTML;
 	fieldb = document.getElementById('row_'+a+'_col_3').innerHTML;
 	fieldc = document.getElementById('row_'+a+'_col_4').innerHTML;
 	fieldd = document.getElementById('row_'+a+'_col_5').innerHTML;

 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	na1 = document.getElementById('row_'+a+'_col_14').innerHTML;
 	na2 = document.getElementById('row_'+a+'_col_15').innerHTML;
 	na3 = document.getElementById('row_'+a+'_col_16').innerHTML;
 	na4 = document.getElementById('row_'+a+'_col_17').innerHTML;

 	totala = Number(fielda) + Number(fieldb) + Number(fieldc) + Number(fieldd);
 	totalna = Number(na1) + Number(na2) + Number(na3) + Number(na4);
 	//alert(totala);
 	totalb = Number(totala);
 	totalnab = Number(totalna);
 	rows = (rowcount * 4) - totalnab;
 	totalc = (totalb / rows) *100;
 	totald = formatNumber(totalc, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totald;
 }
	}



	// Q5 - Q10 Req El's

	for(var a=0; a < ycount; a++)
	{
 for(var f=15; f < 16; f++)
 {
 	var fielda, fieldb, fieldc, fieldd, filede, fieldf
 
 	fielda = document.getElementById('row_'+a+'_col_6').innerHTML;
 	fieldb = document.getElementById('row_'+a+'_col_7').innerHTML;
 	fieldc = document.getElementById('row_'+a+'_col_8').innerHTML;
 	fieldd = document.getElementById('row_'+a+'_col_9').innerHTML;
 	fielde = document.getElementById('row_'+a+'_col_10').innerHTML;
 	fieldf = document.getElementById('row_'+a+'_col_11').innerHTML;

 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	na1 = document.getElementById('row_'+a+'_col_18').innerHTML;
 	na2 = document.getElementById('row_'+a+'_col_19').innerHTML;
 	na3 = document.getElementById('row_'+a+'_col_20').innerHTML;
 	na4 = document.getElementById('row_'+a+'_col_21').innerHTML;
 	na5 = document.getElementById('row_'+a+'_col_22').innerHTML;
 	na6 = document.getElementById('row_'+a+'_col_23').innerHTML;

 	totalna = Number(na1) + Number(na2) + Number(na3) + Number(na4) + Number(na5) + Number(na6);


 	totala = Number(fielda) + Number(fieldb) + Number(fieldc) + Number(fieldd) + Number(fielde) + Number(fieldf);
 	//alert(totala);
 	totalb = Number(totala);
 	totalnab = Number(totalna);
 	rows = (rowcount * 6) - totalnab;
 	totalc = (totalb / rows) *100;
 	totald = formatNumber(totalc, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totald;
 }
	}


	// Q11 - Q12 Closing

	for(var a=0; a < ycount; a++)
	{
 for(var f=16; f < 17; f++)
 {
 	var fielda, fieldb
 
 	fielda = document.getElementById('row_'+a+'_col_12').innerHTML;
 	fieldb = document.getElementById('row_'+a+'_col_13').innerHTML;
 	

 	rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;

 	na1 = document.getElementById('row_'+a+'_col_24').innerHTML;
 	na2 = document.getElementById('row_'+a+'_col_25').innerHTML;
 	

 	totalna = Number(na1) + Number(na2);

 	totala = Number(fielda) + Number(fieldb);
 
 	totalb = Number(totala);
 	totalnab = Number(totalna);
 	rows = (rowcount * 2) - totalnab;
 	totalc = (totalb / rows) *100;
 	totald = formatNumber(totalc, "00.00") + '%';
  
 	document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totald;
 }
	}


}


////////////////// number format script


  // CONSTANTS
 var separator = ","; // use comma as 000's separator
 var decpoint = "."; // use period as decimal point
 var percent = "%";
 var currency = "$"; // use dollar sign for currency

 function formatNumber(number, format, print) { // use: formatNumber(number, "format")
  if (print) document.write("formatNumber(" + number + ", \"" + format + "\")<br>");

  if (number - 0 != number) return null; // if number is NaN return null
  var useSeparator = format.indexOf(separator) != -1; // use separators in number
  var usePercent = format.indexOf(percent) != -1; // convert output to percentage
  var useCurrency = format.indexOf(currency) != -1; // use currency format
  var isNegative = (number < 0);
  number = Math.abs (number);
  if (usePercent) number *= 100;
  format = strip(format, separator + percent + currency); // remove key characters
  number = "" + number; // convert number input to string

   // split input value into LHS and RHS using decpoint as divider
  var dec = number.indexOf(decpoint) != -1;
  var nleftEnd = (dec) ? number.substring(0, number.indexOf(".")) : number;
  var nrightEnd = (dec) ? number.substring(number.indexOf(".") + 1) : "";

   // split format string into LHS and RHS using decpoint as divider
  dec = format.indexOf(decpoint) != -1;
  var sleftEnd = (dec) ? format.substring(0, format.indexOf(".")) : format;
  var srightEnd = (dec) ? format.substring(format.indexOf(".") + 1) : "";

   // adjust decimal places by cropping or adding zeros to LHS of number
  if (srightEnd.length < nrightEnd.length) {
   var nextChar = nrightEnd.charAt(srightEnd.length) - 0;
   nrightEnd = nrightEnd.substring(0, srightEnd.length);
   if (nextChar >= 5) nrightEnd = "" + ((nrightEnd - 0) + 1); // round up

 // patch provided by Patti Marcoux 1999/08/06
   while (srightEnd.length > nrightEnd.length) {
    nrightEnd = "0" + nrightEnd;
   }

   if (srightEnd.length < nrightEnd.length) {
    nrightEnd = nrightEnd.substring(1);
    nleftEnd = (nleftEnd - 0) + 1;
   }
  } else {
   for (var i=nrightEnd.length; srightEnd.length > nrightEnd.length; i++) {
    if (srightEnd.charAt(i) == "0") nrightEnd += "0"; // append zero to RHS of number
    else break;
   }
  }

   // adjust leading zeros
  sleftEnd = strip(sleftEnd, "#"); // remove hashes from LHS of format
  while (sleftEnd.length > nleftEnd.length) {
   nleftEnd = "0" + nleftEnd; // prepend zero to LHS of number
  }

  if (useSeparator) nleftEnd = separate(nleftEnd, separator); // add separator
  var output = nleftEnd + ((nrightEnd != "") ? "." + nrightEnd : ""); // combine parts
  output = ((useCurrency) ? currency : "") + output + ((usePercent) ? percent : "");
  if (isNegative) {
   // patch suggested by Tom Denn 25/4/2001
   output = (useCurrency) ? "(" + output + ")" : "-" + output;
  }
  return output;
 }

 function strip(input, chars) { // strip all characters in 'chars' from input
  var output = ""; // initialise output string
  for (var i=0; i < input.length; i++)
   if (chars.indexOf(input.charAt(i)) == -1)
    output += input.charAt(i);
  return output;
 }

 function separate(input, separator) { // format input using 'separator' to mark 000's
  input = "" + input;
  var output = ""; // initialise output string
  for (var i=0; i < input.length; i++) {
   if (i != 0 && (input.length - i) % 3 == 0) output += separator;
   output += input.charAt(i);
  }
  return output;
 }
</script>

</head>

<body onload="initvalues()">
<div id="wrapper">
	<div id="header">
 <h1>Scholastic QA Report</h1>
	</div>
	
	<div id="padder">
 <div id="content">	
 	<form name="frmdefault" id="frmdefault" action="" method="post">
 	<input type="hidden" name="startdate" value="<%= request.form("startdate") %>" />
 	<input type="hidden" name="enddate" value="<%= request.form("enddate") %>" />
 	<input type="hidden" name="agents" value="<%= request.form("agents") %>" />
 	<input type="hidden" name="ycount" value="<%= y %>" />
 	<input type="hidden" name="fcount" value="<%= fcount %>" />

 	
 	<table align="center" border="1" bordercolor="#334455" cellpadding="2" cellspacing="0">
  <tr>
  	<th colspan="2"><%= startdate %> to <%= enddate %></th>
  	<th colspan="4">Phone Presence / Listening</th>
  	<th colspan="6">Required Elements</th>
  	<th colspan="2">Close Call</th>
  	<th colspan="3">Totals</th>
  </tr>
  <tr>
  	<td class="subhead">CSR Name</td>
  	<td class="subhead">Total Calls Monitored</td>
  	<td class="subhead">Friendly and Enthusiastic (q1)</td>
  	<td class="subhead">Maintains Control (q2)</td>
  	<td class="subhead">Listened, Respected and Responded (q3)</td>
  	<td class="subhead">Apologized (q4)</td>
  	<td class="subhead">Asked Relevant Questions (q5)</td>
  	<td class="subhead">Mentioned Annual Account (q6)</td>
  	<td class="subhead">Ask for Payment (q7)</td>
  	<td class="subhead">Opportunity to Save Account (q8)</td>
  	<td class="subhead">Opportunity to have Customer Keep Product (q9)</td>
  	<td class="subhead">Cross-enroll (q10)</td>
  	<td class="subhead">Ask to assist further / Thanked Customer (q11)</td>
  	<td class="subhead">All Issues resolved (q12)</td>
  	<td class="subhead">Phone Presence / Listening Total Average</td>
  	<td class="subhead">Required Elements / Total Average</td>
  	<td class="subhead">Close Call Total Average</td>
  </tr> 
  
  	<% 
  	for z=0 to y-1
   response.write "<tr>" &vbcrlf
   for f=0 to fcount
   	if f < 17 then
    if f = 0 then
    	response.write "<td id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
    else
    	if f = 1 OR f=14 OR f=15 OR f=16 then
     response.write "<td id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
    	else
     response.write "<td id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
    	end if
    end if
   	else
    response.write "<td style=""visibility: hidden; width:1px;"" id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
   	end if
   next
   response.write "</tr>" &vbcrlf
  	next
  
  	%>
  	
 	</table>
 	
 	<div style="visibility:hidden; height: 1px;">
 	<table>
  <%= tablevar %>
 	</table>
 	</div>
 	</form>
 </div>
	</div>
</div>
</body>
</html>
 
this is a report for agent QA. it selects a bunch of stuff from the db.

the tricick was that it had to be 100% dynamic.
any number of agents, any date range.

here's a screen shot of a result set for 1 agent.

[attachmentid=92]


oh, and here's the SP:

Code:
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER PROCEDURE sp_scholastic_qa_report
(
@startdate datetime,
@enddate datetime,
@agents varchar(350)
)
AS
begin
set nocount off


declare @queryString varchar(8000)
declare @startHolder varchar(30)
declare @endHolder varchar(30)

set @startHolder = fn_dateonly(@startdate)
set @endHolder = fn_dateonly(@enddate)

set @queryString = '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 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 q1 when ''NA'' then 1 else null end) as ''Q1NA'', ' +
'count(case q2 when ''NA'' then 1 else null end) as ''Q2NA'', ' +
'count(case q3 when ''NA'' then 1 else null end) as ''Q3NA'', ' +
'count(case q4 when ''NA'' then 1 else null end) as ''Q4NA'', ' +
'count(case q5 when ''NA'' then 1 else null end) as ''Q5NA'', ' +
'count(case q6 when ''NA'' then 1 else null end) as ''Q6NA'', ' +
'count(case q7 when ''NA'' then 1 else null end) as ''Q7NA'', ' +
'count(case q8 when ''NA'' then 1 else null end) as ''Q8NA'', ' +
'count(case q9 when ''NA'' then 1 else null end) as ''Q9NA'', ' +
'count(case q10 when ''NA'' then 1 else null end) as ''Q10NA'', ' +
'count(case q11 when ''NA'' then 1 else null end) as ''Q11NA'', ' +
'count(case q12 when ''NA'' then 1 else null end) as ''Q12NA'' ' +
'from tScholastic_qa2 ' +
'where cast(txtdate as datetime) between ''' + @startHolder + ''' and ''' + @endHolder + ''' ' +
'and csr in (' + @agents + ') ' +
'group by csr'


exec(@queryString)


end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 

Attachments

  • ss.JPG
    ss.JPG
    84.8 KB · Views: 240
the problem is that ASP sucks and the database was not setup with this kind of reporting in mind.
you can't do math in the database, other than counting, really. its hard to explain.

basically, if i could use php, it would have been 0 javascript, and all trun on the server, taken 1/4 the time to make, 1/5th the time to execute, and 1/1000 the cost of ownership.

MS sucks.
 
JavaScript isn't so bad. My current project is a JavaScript front-end w/ a FileNet/SQL back-end.
The thing w/ JS is that you have to make the java objects you pass to your front-end app ready for user presentation (so you don't have to do a ton of computation on the front-end). Another thing I found useful was using session attributes some times instead of passing over the request/response vars.
 
if it was more than 2 or 3 pages, i would use sessions. but for 3 vars over 2 pages, its just not worth the time :)

ohh, and convo of the day.

Cashizslick: dude, i just had a starteling revolation

Auto response from pissedoffsol: off to work...

Cashizslick: my job > your job
Cashizslick: . . . . yep, nothin beats foldin cloths at the gap
Cashizslick is away at 3:40:35 PM.
Cashizslick returned at 5:12:00 PM.
Cashizslick is idle at 5:30:14 PM.
Cashizslick is no longer idle at 5:33:53 PM.
pissedoffsol: cloths? you ass face. its clothes. enjoy the rest of your life
pissedoffsol: lol
 
:lol:

I just got the general idea from the code that you were pulling values from somewhere and then generating a spreadsheet on the screen after a bit of fuzzy math...
 
uhh....yeah.....sure.....well....see....the.....uhh..... i think i'm gonna leave this all to the smart computer guyz here. i'll go check out the english side of HS
 
Originally posted by pissedoffsol@Jan 17 2005, 04:10 PM
if it was more than 2 or 3 pages, i would use sessions. but for 3 vars over 2 pages, its just not worth the time :)

ohh, and convo of the day.

Cashizslick: dude, i just had a starteling revolation

Auto response from pissedoffsol: off to work...

Cashizslick: my job > your job
Cashizslick:  . . . . yep, nothin beats foldin cloths at the gap
Cashizslick is away at 3:40:35 PM.
Cashizslick returned at 5:12:00 PM.
Cashizslick is idle at 5:30:14 PM.
Cashizslick is no longer idle at 5:33:53 PM.
pissedoffsol: cloths?  you ass face.  its clothes.  enjoy the rest of your life
pissedoffsol: lol

[post=447021]Quoted post[/post]​


Not to mention, "startling" and "revelation". :lmao:
 
work has the say in it, im afraid. if i had my choice, it would be php/oracle.

but, we;re stuck on asp/asp.net/mssql
 
Originally posted by pissedoffsol@Jan 17 2005, 10:11 PM
programming > delivering pizzas
$$$ > Cents

lol why is there no cents key? :p
[post=447180]Quoted post[/post]​


sure thing skippy. I made more money delivering for 35 hours a week than I do now at a "real" job. ;)
 
I have no clue what that is, but :thumbsup: I guess.

I make decent money sitting on a computer putting shit on ebay, selling shit at flea markets and picking up, deliverting and stocking the items. It's a bitch to be travelling all over the tristate area in that stupid van but for $18/ hr I can't complain, cause most of it is either in front of a computer or driving.
 
I hate programmers. they think they're smarter then everyone else.

the sad part about that is....

















most of the time they are
 
Originally posted by pissedoffsol@Jan 18 2005, 09:07 AM
doesn't mean your real job is good :p
[post=447324]Quoted post[/post]​


better than sitting at a desk all day writing nerd code. ;)
 
Back
Top