석차 추출 및 반평균 등등 [ 참고용 ]

<!--++++++++++++++++++++++++++++++++++++++++++++++++++테스트++++++++++++++++++++++++++++++++++++++++++++++++++-->

<table width="550" border="0" cellspacing="1" cellpadding="1" align="center" style="margin-top:30">
<tr><td colspan=3><b>3. 테스트</b></td></tr>
</table>

<table width="550" border="0" cellspacing="1" cellpadding="1" align="center" style="margin-top:5">
 <tr height=25 align=center>
  <td width=90 bgcolor="F2ECDA"><b>월/일 </b></td>
  <td width=80 bgcolor="F2ECDA"><b>종류 </b></td>
  <td width=50 bgcolor="F2ECDA"><b>맞은수</b></td>  
  <td width=50 bgcolor="F2E0DA"><b>점수 </b></td>
  <td width=50 bgcolor="F2E0DA"><b>석차 </b></td>
  <td width=50 bgcolor="F2ECDA"><b>반평균 </b></td>
  <td width=50 bgcolor="F2ECDA"><b>Top </b></td>
  <td width=80 bgcolor="F2ECDA"><b>재테스트</b></td>
 </tr>
 </table>
 
 <table width="550" border="0" cellspacing="1" cellpadding="1" align="center" style="margin-top:5">
 
<% Msql = " select * from sezma_attendance where mb_jumin='"&session_jumin&"'"
 Msql = Msql & " and lecture_month = '"&sukang_date&"' "
 Msql = Msql & " and class_cnt = "& class_cnt &" order by adate desc"
 Set rs = Server.CreateObject("ADODB.Recordset")
 rs.Open Msql, Conn, 1
 
 if rs.EOF or rs.BOF then %>
 
  <tr><td align=center><br><br><br>정보를 찾을 수가 없습니다.</td></tr>
<% else
  i = 1
  Do Until rs.EOF
   
   adate = rs("adate")
   ex_kind1 = rs("ex_kind")
   ex_right = rs("ex_right")
   ex_total = rs("ex_total")
   exam = rs("exam")
   exam2 = rs("exam2")
   
   if ex_kind1 = "0" then
    ex_kind = "확인테스트"
   else
    ex_kind = "모의고사"
   end if
   rank_hidden = rs("rank_hidden")
   
   'sc_mj_ok = rs("sc_mj_ok")
   'sc_mj_total = rs("sc_mj_total")
   'sc_jumsu = rs("sc_jumsu")
   'ban_total = rs("ban_total")
   'ban_place = rs("ban_place")
   'sc_jochi = rs("sc_jochi")
   
   '반total명수를 디비저장
   sSQL = "  select sum(convert(float,exam)) as sum_jumsu, max(convert(float,exam)) as max_jumsu from sezma_attendance"
   sSQL = sSQL & " where adate = '"& adate &"' and class_cnt = '"& class_cnt &"' "
   Set Srs = Server.CreateObject("ADODB.Recordset")
   Srs.Open sSQL, Conn, 1
   
   if Srs.EOF or Srs.BOF then
   else
    'count_mb = Srs("count_mb")
    sum_jumsu  = Srs("sum_jumsu")
    max_jumsu  = Srs("max_jumsu")
   
    '석차
    'ban_ave = Round(sum_jumsu/ban_total, 1)
   
    'response.write "count_jumsu:" & count_jumsu & "<Br>"
    'response.write "sum_jumsu:" & sum_jumsu & "<Br>"
    'response.write "max_jumsu:" & max_jumsu & "<Br><br>"
   end if
   
   qSql = "SELECT exam FROM sezma_attendance WHERE exam <> '' and adate = '"& adate &"' and class_cnt = '"& class_cnt &"' ORDER BY exam desc"
   set qSrs = Server.CreateObject("ADODB.Recordset")
   qSrs.Open qSql, Conn, 1
   
   total_exam = 0
   e_num = 0
   u = 1
   Do until qSrs.EOF
    tmp = Cint(qSrs("exam"))
    total_exam = total_exam + tmp
    e_num = e_num + 1
    if prevalue <> qSrs("exam") then
     if exam = qSrs("exam") then
      rank = u
     end if
    end if
    prevalue = qSrs("exam")
    'response.write prevalue&"|"&qSrs("exam")&"<br>"
    qSrs.MoveNext
   u = u + 1
   Loop
if total_exam <> 0 and e_num <> 0 then  
   ave = round(total_exam / e_num ,1)
end if
%>

 <tr bgcolor=ffffff align=center height=23>
  <td width=90><%=adate%></td>
  <td width=80><%=ex_kind%></td>
<%
if ex_total <> "" or ex_total = "0" then
%>  
  <td width=50><%=ex_right%> / <%=ex_total%></td>
<%
else
%>
  <td width=50> </td>
<%
end if
%>  
  <td width=50><%=exam%></td>
<%
if rank_hidden = "1" then
%>
  <td width=50><%=rank%> / <%=e_num%></td>
<%
else
%>
  <td width=50> </td>
<%
end if
%>
  <td width=50><%=ave%></td>
  <td width=50><%=max_jumsu%></td>
  <td width=80><%=exam2%></td>
 </tr>
 <tr height=1><td bgcolor=cccccc colspan=9></td></tr>
 
 <%  rs.MoveNext
  i = i + 1
  Loop
 
 end if %>
<!-- <tr><td colspan=3 style="padding:10 0 0 10"><b><font color=blue>테스트종합등급 : -->
<%
' Esql = "select sc_grade from score_test where sc_date like '"&sukang_date&"%' and class_cnt="&class_cnt&" and mb_jumin = '"& session_jumin &"'"
' Set Ers = conn.Execute(Esql)
' if not Ers.EOF then
'  sc_grade = Ers("sc_grade")
'  select case sc_grade
'   case 12 : sc_grade_str = "A⁺"
'   case 11 : sc_grade_str = "A⁰"
'   case 10 : sc_grade_str = "A^-"
'   case 9 : sc_grade_str = "B⁺"
'   case 8 : sc_grade_str = "B⁰"
'   case 7 : sc_grade_str = "B^-"
'   case 6 : sc_grade_str = "C⁺"
'   case 5 : sc_grade_str = "C⁰"
'   case 4 : sc_grade_str = "C^-"
'   case 3 : sc_grade_str = "D⁺"
'   case 2 : sc_grade_str = "D⁰"
'   case 1 : sc_grade_str = "D^-"
'   case 0 : sc_grade_str = "F⁺"
'  end select
%>
 <%=sc_grade_str%>
<%
' end if
%>
<!-- </font></b></td></tr>
 <tr><td colspan=9 style="padding:10 0 0 10">
 <table border=0>
 <tr><td><font color=FF9900>A⁺(매우 양호)  </td><td style="padding-left:10"><font color=FF9900>B⁺(비교적 우수) </td><td style="padding-left:10"><font color=FF9900>C⁺ (보통) </td><td style="padding-left:20" colspan=2><font color=FF9900>D⁺(부진)</td></tr>
 <tr><td><font color=FF9900>A⁰(우수)   </td><td style="padding-left:10"><font color=FF9900>B⁰(비교적 우수) </td><td style="padding-left:10"><font color=FF9900>C⁰ (보통) </td><td style="padding-left:20" colspan=2><font color=FF9900>D⁰(부진)</td></tr>
 <tr><td><font color=FF9900>A^-(우수)   </td><td style="padding-left:10"><font color=FF9900>B^-(비교적 우수) </td><td style="padding-left:10"><font color=FF9900>C^- (보통) </td><td style="padding-left:20"><font color=FF9900>D^-(부진)</td><td style="padding-left:20"><font color=FF9900>F(매우 부진)</td></tr>
 </table>
 </font></td></tr>
 </table>
 
 <table width="550" border=0 bgcolor=cccccc cellpadding=1 cellspacing=1>
 <tr height=23 bgcolor=ffffff align=center>
  <td>A⁺</td><td>A⁰</td><td>A^-</td>
  <td>B⁺</td><td>B⁰</td><td>B^-</td>
  <td>C⁺</td><td>C⁰</td><td>C^-</td>
  <td>D⁺</td><td>D⁰</td><td>D^-</td>
  <td>F</td><td bgcolor=f7f7f7>계</td>
 </tr>
 <tr height=23 bgcolor=ffffff align=center>
  <td><%=sc_grade_total12%></td><td><%=sc_grade_total11%></td><td><%=sc_grade_total10%></td>
  <td><%=sc_grade_total9%></td><td><%=sc_grade_total8%></td><td><%=sc_grade_total7%></td>
  <td><%=sc_grade_total6%></td><td><%=sc_grade_total5%></td><td><%=sc_grade_total4%></td>
  <td><%=sc_grade_total3%></td><td><%=sc_grade_total2%></td><td><%=sc_grade_total1%></td><td>

<%=sc_grade_total0%></td>
  <td bgcolor=f7f7f7>

<%=sc_grade_total12+sc_grade_total11+sc_grade_total10+sc_grade_total9+sc_grade_total8+sc_grade_total7+

sc_grade_total6+sc_grade_total5+sc_grade_total4+sc_grade_total3+sc_grade_total2+sc_grade_total1+sc_grade_total0%>

</td>
 </tr>
-->
 </table>