기준일 현황
<%
query1 = ""
query1 = query1 & " SELECT "
query1 = query1 & " CONVERT(varchar(10), GETDATE(), 120) AS BASE_DT, "
query1 = query1 & " COUNT(*) AS TOTAL_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'Y' THEN 1 ELSE 0 END) AS USE_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'N' THEN 1 ELSE 0 END) AS AVAIL_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '01' THEN 1 ELSE 0 END) AS LOST_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '02' THEN 1 ELSE 0 END) AS BROKEN_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '03' THEN 1 ELSE 0 END) AS FAIL_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '04' THEN 1 ELSE 0 END) AS STOP_CNT "
query1 = query1 & " FROM project2.dbo.T_DEVICE_NEW "
adors.open query1, adoconn, adopenstatic, adlockreadonly
%>
| 기준일 |
총수량 |
사용중 |
미사용 |
대여불가 |
| 분실 |
파손 |
고장 |
정지 |
| <%=adors("BASE_DT")%> |
<%=adors("TOTAL_CNT")%> |
<%=adors("USE_CNT")%> |
<%=adors("AVAIL_CNT")%> |
<%=adors("LOST_CNT")%> |
<%=adors("BROKEN_CNT")%> |
<%=adors("FAIL_CNT")%> |
<%=adors("STOP_CNT")%> |
<% adors.close %>
프로젝트별 분출 현황
<%
query2 = ""
query2 = query2 & " SELECT "
query2 = query2 & " D.P_CHILD_SN, "
query2 = query2 & " ISNULL(VPM.P_SUBJECT, '') AS P_SUBJECT, "
query2 = query2 & " COUNT(*) AS OUT_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '53' THEN 1 ELSE 0 END) AS STAFF_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun IN ('10','21','31','41','42','43','44','45','46','51','52') THEN 1 ELSE 0 END) AS SEOUL_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '12' THEN 1 ELSE 0 END) AS BUSAN_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '13' THEN 1 ELSE 0 END) AS DAEGU_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '14' THEN 1 ELSE 0 END) AS DAEJEON_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '15' THEN 1 ELSE 0 END) AS GWANGJU_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '16' THEN 1 ELSE 0 END) AS GANGWON_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '17' THEN 1 ELSE 0 END) AS JEJU_CNT "
query2 = query2 & " FROM project2.dbo.T_DEVICE_NEW D "
query2 = query2 & " LEFT JOIN project2.dbo.man M ON D.MAN_ID = M.new_id "
query2 = query2 & " LEFT JOIN project2.dbo.T_PROJECT_METHOD VPM ON D.P_CHILD_SN = VPM.p_child_sn "
query2 = query2 & " WHERE D.P_CHILD_SN <> '' "
query2 = query2 & " GROUP BY D.P_CHILD_SN, VPM.P_SUBJECT "
query2 = query2 & " ORDER BY D.P_CHILD_SN "
adors.open query2, adoconn, adopenstatic, adlockreadonly
Dim rowNo : rowNo = 0
Dim totalOut, totalStaff, totalSeoul, totalBusan, totalDaegu, totalDaejeon, totalGwangju, totalGangwon, totalJeju
totalOut = 0 : totalStaff = 0 : totalSeoul = 0 : totalBusan = 0 : totalDaegu = 0 : totalDaejeon = 0 : totalGwangju = 0 : totalGangwon = 0 : totalJeju = 0
%>
| No |
프로젝트 코드 |
프로젝트명 |
분출수량 |
분출처 |
| 직원 |
서울 |
지방실사 |
| 부산 |
대구 |
대전 |
광주 |
강원 |
제주 |
<% Do While Not adors.Eof
rowNo = rowNo + 1
%>
| <%=rowNo%> |
<%=adors("P_CHILD_SN")%> |
<%=adors("P_SUBJECT")%> |
<%=adors("OUT_CNT")%> |
<%=adors("STAFF_CNT")%> |
<%=adors("SEOUL_CNT")%> |
<%=adors("BUSAN_CNT")%> |
<%=adors("DAEGU_CNT")%> |
<%=adors("DAEJEON_CNT")%> |
<%=adors("GWANGJU_CNT")%> |
<%=adors("GANGWON_CNT")%> |
<%=adors("JEJU_CNT")%> |
<%
totalOut = totalOut + adors("OUT_CNT")
totalStaff = totalStaff + adors("STAFF_CNT")
totalSeoul = totalSeoul + adors("SEOUL_CNT")
totalBusan = totalBusan + adors("BUSAN_CNT")
totalDaegu = totalDaegu + adors("DAEGU_CNT")
totalDaejeon = totalDaejeon + adors("DAEJEON_CNT")
totalGwangju = totalGwangju + adors("GWANGJU_CNT")
totalGangwon = totalGangwon + adors("GANGWON_CNT")
totalJeju = totalJeju + adors("JEJU_CNT")
adors.MoveNext
Loop
adors.close
%>
| 총계 |
<%=totalOut%> |
<%=totalStaff%> |
<%=totalSeoul%> |
<%=totalBusan%> |
<%=totalDaegu%> |
<%=totalDaejeon%> |
<%=totalGwangju%> |
<%=totalGangwon%> |
<%=totalJeju%> |