1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

i just wrote an aweful stored procedure...

Discussion in 'Computers, Games, Electronics etc' started by pissedoffsol, May 18, 2005.

  1. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    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
     
  2. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    i guess no one cares :( where's the nerds at? oh yeah, star wars opens tonight... :bash:
     
  3. Battle Pope

    Battle Pope New Member

    Messages:
    10,279
    Likes Received:
    60
    Joined:
    Jun 22, 2004
    Location:
    Southern Oregon Coast
    grats. want a cookie?

    It's all gibberish to me. :)
     
  4. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
  5. spectacle

    spectacle Senior Member

    Messages:
    898
    Likes Received:
    0
    Joined:
    Aug 23, 2003
    Location:
    St. Petersburg, Fl
    you're entirely too fucking smart.
     
  6. CRX-YEM

    CRX-YEM Super Moderator Moderator VIP

    Messages:
    4,623
    Likes Received:
    54
    Joined:
    Sep 29, 2002
    Location:
    Wallingford, CT
    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 :)
     
  7. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    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.



    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 :)
     
  8. spectacle

    spectacle Senior Member

    Messages:
    898
    Likes Received:
    0
    Joined:
    Aug 23, 2003
    Location:
    St. Petersburg, Fl
    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. :)
     
  9. VTECin5th

    VTECin5th Administrator

    Messages:
    2,182
    Likes Received:
    4
    Joined:
    Apr 17, 2005
    Location:
    Phoenix Az
  10. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    Code:
    killPerl()
    if (Reikoshea == loving perl)
    {
      digGrave();
    }
    else if (Reikoshea != loving perl)
    {
      codeJava();
    }
    
     
  11. xj0hnx

    xj0hnx I wanna be sedated VIP

    Messages:
    14,172
    Likes Received:
    48
    Joined:
    Nov 10, 2002
    Location:
    C.C.TX.
    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)
     
  12. VTECin5th

    VTECin5th Administrator

    Messages:
    2,182
    Likes Received:
    4
    Joined:
    Apr 17, 2005
    Location:
    Phoenix Az
    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;
     
  13. VTECin5th

    VTECin5th Administrator

    Messages:
    2,182
    Likes Received:
    4
    Joined:
    Apr 17, 2005
    Location:
    Phoenix Az
    Code:
    if ($perl > $java){
    &ReikoWrong;
    }
    if ($perl < $java){
    &never;
    }
    sub never{
    }
    sub ReikoWrong{
    print "perl is Always > Java";
    }
     
  14. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    perl sucks. who still uses it? lol

    java ... ugh... i hate java.

    OOP owns me... i suck at it.
     
  15. TurboMirage

    TurboMirage YEEAAAHHH VIP

    Messages:
    24,577
    Likes Received:
    696
    Joined:
    May 20, 2003
    Location:
    Central, MA
    haha you guys are great.
     
  16. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    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.
     
  17. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    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
     
  18. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    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.
     
  19. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    53
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    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
     
  20. VTECin5th

    VTECin5th Administrator

    Messages:
    2,182
    Likes Received:
    4
    Joined:
    Apr 17, 2005
    Location:
    Phoenix Az
    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
     
Verification:
Draft saved Draft deleted

Share This Page