i just wrote an aweful stored procedure...

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

posol

Staff member
god this thing sucked... lol
so much, that i decided to post it :p


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 :p
 

CRX-YEM

Moderator
VIP
Code:
 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

god damn, that must of been so much fun :)
 

posol

Staff member
that was the easy part...

that's just simple addition of the 11 questions, catching the case for whether it was a valid "1" point or not.

the hard part was all the casting, making the whole numbers floats so that the percentage actually did something (otherwise it would come up as 1 for .5 and greater or 0 for .49 and lower).

sql is kinda gay.

i mean, that whole section would have been SOOO much easier if once a select is given an id-- foo AS bar -- you sould be able to use "bar" in applied math... seeing as i already 'selected' the values for it...

in this case, to get the percent...

cast([Total Points] as FLOAT) / cast([Adjusted Total Questions] as FLOAT)

done.

but no, sql won't recognize that. you'll get "invalid column" errors, as theres no column in the table called such a thing.

I guess I could have used a temp table...

create a temp table to hold total points, total na, adjusted points, and the final percentage
select from that temp table, perform an enum() function on the fields to fill in the later two rows...
but that just seemed more complicated.

another option was to make that big division section a seperate function... but then later down the road you will see fn_getnumertor() / fn_getdemoninator() and it will make no sense... lol

so, all in all, i think i attacked it the best way.

you're entirely too fucking smart.


bah, it looks a lot more difficult than it really is.

basically what it comes down to is that you can't do math on a string... basically, its saying "two + two" instead of 2+2. "two + two" would equal out to be "twotwo" instead of 4.

so you have to CAST that string into an integer (int -- whole numbers) or a float (real numbers -- i think its reals... includes everything and decimals, both pos and neg-- )

if i used an int, as i said above, it would round to the nearest whole number. since were derriving percents, its between 1 and 0... like .80 would be 80%. but since .8 insn't valid, it rounds by default to a 1. 40% woudl round to a 0. so, you need to cast both the num. and the dem. as floats, even though they aer whole numbers, before the division, so that you can get a real decimal answer.

but, sql is gay again and it doesn't like PRINTING or exporting a value as a float... it will tend to round itself-- or more likely, truncate.
for example, 83.439223% would end up showing up as 83.43% instead of its REAL rounded value of 83.44%.

lol

if you still think thats confusing, well, thts why i make the big bucks :p

i learned none of this in college.... i'm pretty much self-tought in everything i know about programming and database administration. if you have the will, you can learn it to.

http://w3schools.com can get you off to a great start :)
 
see here's the difference...you're one of the few that actually deserve to get paid what you make, that's why you make the big bucks. people as smart as you should be making more then the fuckers I see running around the IT field acting like they know shit but in reality they're just somebody who knows somebody that got them hired for a position they're not qualified for.


I do firewall administration Pissedoff...Spectacle doesn't fuck with numbers unless they're IP addresses. :)
 

reikoshea

Moderator
VIP
Code:
killPerl()
if (Reikoshea == loving perl)
{
  digGrave();
}
else if (Reikoshea != loving perl)
{
  codeJava();
}
 

xj0hnx

VIP
Code:
No idea()

if (this post)=//technical jargon)
(1=brain explodes)(2=convulsions)
---tremors
perl(=huh?)
can't breathe(23=death)(ifcode=continues)
http=logging/out(going=driving)
(25=clearinghead)
tires(line 30=sqealling tires/burntrubber)
(than 8000rpm(>vtec)
(31=happiness)
 
sub No_Idea{
$1 = "brain explodes";
$2 = "convulsions";
$23 = "death";
$25 = "clearinghead";
$31 = "happiness";
$VTEC = "$chipped_ecu[7]";
if ($this_post =~ 'technical jargon'){
print "$1 $2";
# ---tremors
# perl(=huh?)
}
if ($life =~ 'can\'t breathe'){
print "$23";
if($code =~ 'continues'){
print "http=logging/out(going=driving)";
}
print "$25";
}
if ($tirepressure > 28){
print "squeel";
}
else {
print "traction";
}
if ($rpm > $VTEC && $ecu =~ 'chipped'){
print "$31";
}
if ($rpm > $VTEC && $ecu =~ 'p28'){
print "DETONATION";
}
}
exit;
 
Code:
if ($perl > $java){
&ReikoWrong;
}
if ($perl < $java){
&never;
}
sub never{
}
sub ReikoWrong{
print "perl is Always > Java";
}
 

reikoshea

Moderator
VIP
yeah, i probably should learn some of that stuff...but the combination of javascript and CFM had done me well. I can do ALMOST anything that can be done in ASP/PHP.

Only problem is that i cant find a host that will host with cold fusion.
 

posol

Staff member
cfml is expensive.
php is free
asp sucks, but im forced to use it at work...
but slowly, we're getting some linux servers with apache on them....

php + odbc here i come :p
 

reikoshea

Moderator
VIP
well...i just dont have any of the free time i need to learn something new. We use CFM at work (strangely enough the company is called CFM too) just because the webserver uses such little bandwidth because all we are doing is allowing our customers to update their personal database. If i was running a message board on CFM, id die because of the cost for a CFM host.

IIS w/ ColdFusion on a T1 connection is good for now.
 

posol

Staff member
IIS sucks.. lol i reboot iis at least 3 times a week on various serers here at the office.

HS is on apache... and we've rebooted it like once, when we did an update and needed to recomplie it. lol
 
yea IIS does suck, i ran iis for about 9 months, and thought it was good, until i got a new computer and installed apache+xp home, and it's way more stable.
And perl owns everything, no one uses it anymore, it's more of an exploit language now, and works damn well too. But i can do anything and more, that php can do, usually much easier! :p
 
Top