Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change duplicate code to call function????

    I am having some formula use in one asp page which is repeated but for dirrefent sql query, my question is how can I change the formula to an function so that I can call out that formula for calculation and don't need to repeat my formula on every sql which it will make my code become vety long & mess.... my code are as below,
    Code:
    SET RS=CreateObject("ADODB.Recordset") 'create a recordset
    sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"   
    'start from here are my formula which I will use for 2, 3 and so on sql....
    RS.Open sql,conn,1,2
    if rs.recordcount < 1 Then 
    Count = "No Data"
    atavg1 = 0
    atdiffavg1 = 0
    UCL = 0
    LCL = 0
    Range1ave = 0
    Rucl = 0
    Rlcl = 0
    cpk = 0
    cp = 0
    else
    if rs.recordcount  >= 30 then
    SET RS=CreateObject("ADODB.Recordset") 'create a recordset 
    sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC" 
    else
    SET RS=CreateObject("ADODB.Recordset") 'create a recordset
    sql="select Top 30 Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek <= " & tEnd & " and Year = " & tYear & " Order by workweek DESC" 
    end if
    Rs.open sql,conn,1,2
    prioravg1 = -1 
    tavg1=0
    tdiffavg1=0
    trange1=0
    count=0%>
    
    <%
    Do until rs.eof
    avg1 = RS("avg1") 
    range1= rs("range1")
    diffavg1 = 0 
    If prioravg1 >= 0 Then diffavg1 = ABS( avg1 - prioravg1 ) 'get the mrange of every avg1 and avg1
    '===========================================================================================
    count = count + 1
    trange1= trange1 + range1
    Range1ave= trange1/ count ' Get the range Average
    Rucl = 1.652 * range1ave ' get the range chart UCL
    Rlcl = 0.348 *  range1ave' get the range chart LCL
    sigma = range1ave / 3.472 ' get the sigma for sample size =15 = 3.472 
    sixsigma = 6 * sigma
    specmean = 800 - 300 ' USl - LSL
    cp= specmean / sixsigma ' get the process cp
    
    '===============================================
    tavg1 = tavg1 + avg1 
    tdiffavg1 = tdiffavg1 + diffavg1
    atavg1= tavg1 / count ' get the average of avg1
    if rs.recordcount > 1 then Rcount = rs.recordcount - 1
    if rs.recordcount = 1 then Rcount = 1
    atdiffavg1= tdiffavg1  / Rcount ' /////////' get the average of moving range 
    '=======================================================
    UCL = atavg1 + (2.66 * atdiffavg1) ' get the UCL
    LCL = atavg1 - (2.66 * atdiffavg1)  ' get the LCL
    '===================================================
    'Below code is to get the CPK
    USL = 800 ' upper spec limit
    LSL = 300 ' lower spec limit
    tsigma= 3 * sigma ' get 3 sigma to calculate the cpk
    UCpu = USL - atavg1 ' USL = 800 - atavg1
    Cpu =  UCpu / tsigma  ' get the cpu 
    UCpl = atavg1 - LSL 'atavg1 - LSL = 300
    Cpl = UCpl / tsigma ' get cpl0u 
    if Cpu > Cpl then Cpk = Cpl ' compare cpu and cpl get the lower value as cpk 
    if Cpl >= Cpu then Cpk = Cpu
    
    prioravg1=avg1
    rs.movenext
    Loop
    End if%>
    'end of my formula export the calculation to a table:
    <tr>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkgid%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atavg1,2)%></font></Td>  
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atdiffavg1,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(UCL,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(LCL,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=count%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Range1ave,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rucl,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rlcl,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Formatnumber(cpk,2)%></font></Td>
      <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(cp,2)%></font></Td>
     </tr>
     <%rs.close
    %>
    
    ' start a new sql  
    SET RS=CreateObject("ADODB.Recordset") 'create a recordset
    sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Like '%208' and Pkgtype Not Like '%G208' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"   
    RS.Open sql,conn,1,2
    'repeat the above formula & do calculation then export the data to a table after that follow by another sql & so on....
    appreciated if some one will help me on this, so that my asp code will not as a mess....

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    The easiest way to do this is to use a sub simply save the code that you re-run in subroutine then call the sub each time you want to use it.
    Code:
    Sub Formula()
    	RS.Open sql,conn,1,2
    	if rs.recordcount < 1 Then 
    		Count = "No Data"
    		atavg1 = 0
    		atdiffavg1 = 0
    		UCL = 0
    		LCL = 0
    		Range1ave = 0
    		Rucl = 0
    		Rlcl = 0
    		cpk = 0
    		cp = 0
    	else
    		if rs.recordcount  >= 30 then
    			SET RS2=CreateObject("ADODB.Recordset") 'create a recordset 
    			sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC" 
    		else
    			SET RS2=CreateObject("ADODB.Recordset") 'create a recordset
    			sql="select Top 30 Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek <= " & tEnd & " and Year = " & tYear & " Order by workweek DESC" 
    		end if
    		Rs.open sql,conn,1,2
    		prioravg1 = -1 
    		tavg1=0
    		tdiffavg1=0
    		trange1=0
    		count=0
    		Do until rs.eof
    			avg1 = RS("avg1") 
    			range1= rs("range1")
    			diffavg1 = 0 
    			If prioravg1 >= 0 Then diffavg1 = ABS( avg1 - prioravg1 ) 'get the mrange of every avg1 and avg1
    	'===========================================================================================
    			count = count + 1
    			trange1= trange1 + range1
    			Range1ave= trange1/ count ' Get the range Average
    			Rucl = 1.652 * range1ave ' get the range chart UCL
    			Rlcl = 0.348 *  range1ave' get the range chart LCL
    			sigma = range1ave / 3.472 ' get the sigma for sample size =15 = 3.472 
    			sixsigma = 6 * sigma
    			specmean = 800 - 300 ' USl - LSL
    			cp= specmean / sixsigma ' get the process cp
    			
    			'===============================================
    			tavg1 = tavg1 + avg1 
    			tdiffavg1 = tdiffavg1 + diffavg1
    			atavg1= tavg1 / count ' get the average of avg1
    			if rs.recordcount > 1 then Rcount = rs.recordcount - 1
    			if rs.recordcount = 1 then Rcount = 1
    			atdiffavg1= tdiffavg1  / Rcount ' /////////' get the average of moving range 
    			'=======================================================
    			UCL = atavg1 + (2.66 * atdiffavg1) ' get the UCL
    			LCL = atavg1 - (2.66 * atdiffavg1)  ' get the LCL
    			'===================================================
    			'Below code is to get the CPK
    			USL = 800 ' upper spec limit
    			LSL = 300 ' lower spec limit
    			tsigma= 3 * sigma ' get 3 sigma to calculate the cpk
    			UCpu = USL - atavg1 ' USL = 800 - atavg1
    			Cpu =  UCpu / tsigma  ' get the cpu 
    			UCpl = atavg1 - LSL 'atavg1 - LSL = 300
    			Cpl = UCpl / tsigma ' get cpl0u 
    			if Cpu > Cpl then Cpk = Cpl ' compare cpu and cpl get the lower value as cpk 
    			if Cpl >= Cpu then Cpk = Cpu
    			
    			prioravg1=avg1
    			rs.movenext
    		Loop
    	End if%>
    	'end of my formula export the calculation to a table:
    	<tr>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkgid%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atavg1,2)%></font></Td>  
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atdiffavg1,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(UCL,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(LCL,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=count%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Range1ave,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rucl,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rlcl,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Formatnumber(cpk,2)%></font></Td>
    	  <Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(cp,2)%></font></Td>
    	 </tr>
    	<%
       rs.close
    End Sub
    
    
    
    SET RS=CreateObject("ADODB.Recordset") 'create a recordset
    sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"   
    Call formula()
    
    ' start a new sql 
    'SET RS=CreateObject("ADODB.Recordset") 'create a recordset
    sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Like '%208' and Pkgtype Not Like '%G208' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"   
    RS.Open sql,conn,1,2
    Call Formula()
    You do not need to recreate a recordset object each time you want to use it I hope when you are done that you close all of these and set all of your objects to nothing otherwise your server will reserve memory for these objects.

    You should also use Server.CreateObject instead of CreateObject

    also it look like you have a recordset nested inside of another in this case you will need to use 2 differently named recordset objects. In vb, unlike C type languages, merely changing capitalization of one doesnt change their names.
    Last edited by miranda; 09-02-2005 at 07:04 PM.

  • #3
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tks a lot for your help and advise....Appreciated..


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •