asked on
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
"( " & _
"(select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE2) from tblStaff where Title2 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE3) from tblStaff where Title3 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE4) from tblStaff where Title4 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE5) from tblStaff where Title5 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE6) from tblStaff where Title6 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE7) from tblStaff where Title7 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE8) from tblStaff where Title8 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE9) from tblStaff where Title9 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) + " & _
"(select SUM(FTE10) from tblStaff where Title10 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) " & _
") AS SUMTechsFilled, " & _
"( " & _
"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title2 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title3 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title4 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title5 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title6 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title7 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title8 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title9 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title10 LIKE '%Librarian%' and ChartID=t_staff.ChartID) " & _
") AS cntLibrary, " & _
"( " & _
"(select count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus2 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus3 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus4 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus5 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus6 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus7 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus8 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus9 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus10 LIKE '%Vacant%' and ChartID=t_staff.ChartID) " & _
") AS cntVacant, " & _
"( " & _
"(select count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title2 NOT LIKE '%Librarian%') and (Title2 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title3 NOT LIKE '%Librarian%') and (Title3 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title4 NOT LIKE '%Librarian%') and (Title4 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title5 NOT LIKE '%Librarian%') and (Title5 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title6 NOT LIKE '%Librarian%') and (Title6 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title7 NOT LIKE '%Librarian%') and (Title7 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title8 NOT LIKE '%Librarian%') and (Title8 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title9 NOT LIKE '%Librarian%') and (Title9 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title10 NOT LIKE '%Librarian%') and (Title10 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) " & _
") AS cntOther " & _
"FROM tblGeneral INNER JOIN tblStaff t_staff ON tblGeneral.ChartID= t_staff.ChartID "
If Request.querystring("sort") = "" then
sql2 = sql2 & "ORDER BY City"
Else
sql2 = sql2 & "ORDER BY " & Request.querystring("sort")
End If
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open sql2, objConn
ASKER
ASKER
ASKER
ASKER
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10 " & _
"( " & _
"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2) " & _
") AS SUMTechsFilled, " & _
"( " & _
"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + "
ASKER
ASKER
ASKER
<!doctype html public "-//w3c//dtd html 4.0 strict//en">
<!-- #Include Virtual="/CodeReuse/ConnComparisonChart.inc" -->
<%
dim title
title = "Comparison Chart. Staffing (FTE and On-Board) Overview"
%>
<%
'Create recordset
Dim sql2, objRS2, counter
counter = 0
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
"( " & _
"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) " & _
") AS SUMTechsFilled, " & _
"( " & _
"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title2 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title3 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title4 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title5 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title6 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title7 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title8 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title9 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title10 LIKE '%Librarian%' and ChartID=t_staff.ChartID) " & _
") AS cntLibrary, " & _
"( " & _
"(select count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus2 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus3 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus4 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus5 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus6 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus7 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus8 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus9 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus10 LIKE '%Vacant%' and ChartID=t_staff.ChartID) " & _
") AS cntVacant, " & _
"( " & _
"(select count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title2 NOT LIKE '%Librarian%') and (Title2 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title3 NOT LIKE '%Librarian%') and (Title3 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title4 NOT LIKE '%Librarian%') and (Title4 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title5 NOT LIKE '%Librarian%') and (Title5 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title6 NOT LIKE '%Librarian%') and (Title6 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title7 NOT LIKE '%Librarian%') and (Title7 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title8 NOT LIKE '%Librarian%') and (Title8 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title9 NOT LIKE '%Librarian%') and (Title9 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title10 NOT LIKE '%Librarian%') and (Title10 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) " & _
") AS cntOther " & _
"FROM tblGeneral INNER JOIN tblStaff t_staff ON tblGeneral.ChartID= t_staff.ChartID "
If Request.querystring("sort") = "" then
sql2 = sql2 & "ORDER BY City"
Else
sql2 = sql2 & "ORDER BY " & Request.querystring("sort")
End If
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open sql2, objConn
'------------------------------------------
'COUNT NUMBER OF FACILITIES REPORTING DATA
'------------------------------------------
sqlCountChartID = "SELECT COUNT(tblStaff.ChartID) AS CountChartID " & _
"FROM tblStaff "
Set objCountChartID = Server.CreateObject("ADODB.Recordset")
objCountChartID.Open sqlCountChartID, objConn
'------------------------------------------
'CALCULATIONS FOR FTE
'------------------------------------------
sqlSumLibFTE = "SELECT Sum(tblStaff.LibFTE) AS SumLibFTE " & _
"FROM tblStaff "
Set objSumLibFTE = Server.CreateObject("ADODB.Recordset")
objSumLibFTE.Open sqlSumLibFTE, objConn
'The following includes the records with 0 responses and then calculates an average
sqlAvgLibFTE = "SELECT SUM(LibFTE)/COUNT(LibFTE) AS AvgLibFTE " & _
"FROM tblStaff "
Set objAvgLibFTE = Server.CreateObject("ADODB.Recordset")
objAvgLibFTE.Open sqlAvgLibFTE, objConn
'------------------------------------------
'CALCULATION FOR OnBoardFTE
'------------------------------------------
sqlSumOnBoardFTE = "SELECT Sum(tblStaff.OnBoardFTE) AS SumOnBoardFTE " & _
"FROM tblStaff "
Set objSumOnBoardFTE = Server.CreateObject("ADODB.Recordset")
objSumOnBoardFTE.Open sqlSumOnBoardFTE, objConn
sqlAvgOnBoardFTE = "SELECT SUM(OnBoardFTE)/COUNT(OnBoardFTE) AS AvgOnBoardFTE " & _
"FROM tblStaff "
Set objAvgOnBoardFTE = Server.CreateObject("ADODB.Recordset")
objAvgOnBoardFTE.Open sqlAvgOnBoardFTE, objConn
'--------------------------------
'Percentage of FILLED positions
'--------------------------------
sqlFilled = "SELECT SUM(OnBoardFTE) / SUM(LibFTE) * 100 AS Filled " & _
"FROM tblStaff "
Set objFilled = Server.CreateObject("ADODB.Recordset")
objFilled.Open sqlFilled, objConn
'--------------------------------
'No of VACANCIES
'--------------------------------
sqlVacancies = "SELECT SUM(LibFTE) - SUM(OnBoardFTE) AS Vacancies " & _
"FROM tblStaff "
Set objVacancies = Server.CreateObject("ADODB.Recordset")
objVacancies.Open sqlVacancies, objConn
if err.number <> 0 then
response.write err.description
end if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta http-equiv="Subject" content="Library Orientation Evaluation">
<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">
<title><%=title%></title>
<link rel=stylesheet type="text/css"
href="/VALNET/CodeReuse/style.css">
</head>
<body>
<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>
<%
Response.Write "<ul>"
Response.Write "<li><font face='Arial' font size=2'>Total number of facilities reporting data: "
Response.write (objCountChartID("CountChartID"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size=2'>TOTAL Approved FTE Staffing: "
Response.write Formatnumber(objSumLibFTE("SumLibFTE"),2)
Response.write "<i> (" & Formatnumber(objFilled("Filled"),0) & "% filled)</i>"
Response.Write "<li><font face='Arial' font size=2'>Average Approved FTE Staffing Level: "
Response.write Formatnumber(objAvgLibFTE("AvgLibFTE"),2)
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size=2'>TOTAL Actual (on-board) FTE Staffing: "
Response.write Formatnumber(objSumOnBoardFTE("SumOnBoardFTE"),2)
Response.Write "<li><font face='Arial' font size=2'>Average Actual (on-board) FTE Staffing Level: "
Response.write Formatnumber(objAvgOnBoardFTE("AvgOnBoardFTE"),2)
Response.Write "<li><font face='Arial' font size=2'>TOTAL number of Vacancies: "
Response.write Formatnumber(objVacancies("Vacancies"),2)
Response.Write "</li></ul>"
Response.Write "<hr>"
%>
* Indicates a consolidated facility, no separate complexity level assigned.<br>
# A count of the number of positions in this category (not FTE)
<%
'First, display the TABLE header info:
Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='2' width='1000px'>"
'Display table headings for each column in the Recordset
Response.Write "<TR style='position:absolute;top:264px;background-color:white;'>"
Response.Write "<TH class='CCTable' scope='col' width='25px'> </th>"
Response.Write "<TH class='CCTable' scope='col' width='172px'><a href='StaffingPositions.asp?sort=City'><font size='1' color='blue'><b>Location</b></a></font></th></th>"
Response.Write "<TH class='CCTable' scope='col' width='85px'><a href='StaffingPositions.asp?sort=Facility'><font size='1' color='blue'><b>Facility</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='50px'><a href='StaffingPositions.asp?sort=VISNID'><font size='1' color='blue'><b>VISN</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=Complexity'><font size='1' color='blue'><b>Complexity</a> *</b><br><a target='_blank' href='/Valnet/ComparisonChart/ComplexityLevels.htm'><font size='1'>What is this?</a></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='70px'><a href='StaffingPositions.asp?sort=LibFTE'><font size='1' color='blue'><b>Approved<br>Library<br>FTE</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='72px'><font size='1' color='blue'><b>No. of<br>Librarian<br>Positions #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>Tech<br>Positions #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>All Other<br>Titles #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=OnBoardFTE'><font size='1' color='blue'><b>Actual<br>Library<br>FTE</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>Vacancies #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='250px'><font size='1' color='blue'><b>General<br>Staff<br>Notes</b></font></th></tr></TABLE>"
Response.Write "<div style='OVERFLOW:auto; HEIGHT:400px; position:absolute;top:308px;width:1025px'>"
Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='0' width='1000px'>"
While Not objRS2.EOF
counter = counter + 1
Response.Write "<TR><TD class='CCTable' scope='row' width='25px'>" & counter
Response.Write "<TD class='CCTable' scope='row' width='148px'>" & objRS2("City") & ", " & objRS2("State") & "<font color='#FF0000'><b>" & "</FONT></B></TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("Facility") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='50px'>" & objRS2("VISNID") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='80px'>" & objRS2("Complexity") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("LibFTE") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntLibrary") & "</TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("SumTechsFilled") & "</TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntOther") & "</TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("OnBoardFTE") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntVacant") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='218px'>" & objRS2("StaffGenNotes") & " </TD>" & VbCrLf
'Advance the recordset
objRS2.MoveNext
Wend
objRS2.Close
Set objRS2 = Nothing
Response.Write "</td></TABLE>" & vbCrLf
Response.Write "</div>"
%>
</body>
</html>
Dim sql, rs, i, RowCounter, HitCounter
'' ## Loop 10 times to get the
for i = 1 to 10
sql = sql & "(Title" & i & " LIKE '%Techn%' AND PersonStatus='Filled') OR "
next
'' ## Trim the last "OR"
sql = Left(sql, len(sql)-4)
'' ## Complete the sql Statement
sql = "SELECT Fields FROM Tables WHERE (" & sql & ") AND ChartID=t_staff.ChartID"
'' ## Collect the likely data
Set rs = Conn.execute(sql)
'' ## check we have a result at all
if NOT(rs.EOF) then
while NOT(rs.EOF)
RowCounter = RowCounter + 1
'' ## Check every one from 1 to 10
for i = 1 to 10
'' ## Only do the adding if it maches our type
if InStr(rs("Title" & i), "Techn") then
TheSum = TheSum + rs("FTE" & i)
HitCounter = HitCounter + 1
end if
next
rs.movenext
wend
end if
rs.close
Set rs = nothing
'' ## The variable TheSum will now have the value 1.5, as per the original question.
ASKER
<%
'Create recordset
Dim sql2, objRS2
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, tblGeneral.Complexity, " & _
"tblGeneral.Facility, t_staff.LibFTE, t_staff.OnBoardFTE, t_staff.StaffGenNotes, " & _
"t_staff.Title1, t_staff.Title2, t_staff.Title3, t_staff.Title4, t_staff.Title5, " & _
"t_staff.Title6, t_staff.Title7, t_staff.Title8, t_staff.Title9, t_staff.Title10, " & _
"t_staff.FTE1, t_staff.FTE2, t_staff.FTE3, t_staff.FTE4, t_staff.FTE5, " & _
"t_staff.FTE6, t_staff.FTE7, t_staff.FTE8, t_staff.FTE9, t_staff.FTE10, " & _
"t_staff.PositionStatus1, t_staff.PositionStatus2, t_staff.PositionStatus3, " & _
"t_staff.PositionStatus4, t_staff.PositionStatus5, t_staff.PositionStatus6, " & _
"t_staff.PositionStatus7, t_staff.PositionStatus8, t_staff.PositionStatus9, t_staff.PositionStatus10, " & _
"( " & _
"cast((select SUM(FTE1) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus1 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE2) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus2 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE3) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus3 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE4) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus4 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE5) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus5 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE6) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus6 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE7) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus7 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE8) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus8 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE9) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus9 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) + " & _
"cast((select SUM(FTE10) from tblStaff where Title1 LIKE '%Techn%' and PositionStatus10 = 'Filled' AND ChartID=t_staff.ChartID) AS decimal(10,2)) " & _
") AS SUMTechsFilled, " & _
"( " & _
"(select count(1) from tblStaff where Title1 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + "
"(select count(1) from tblStaff where Title2 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title3 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title4 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title5 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title6 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title7 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title8 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title9 LIKE '%Librarian%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where Title10 LIKE '%Librarian%' and ChartID=t_staff.ChartID) " & _
") AS cntLibrary, " & _
"( " & _
"(select count(1) from tblStaff where PositionStatus1 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus2 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus3 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus4 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus5 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus6 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus7 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus8 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus9 LIKE '%Vacant%' and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where PositionStatus10 LIKE '%Vacant%' and ChartID=t_staff.ChartID) " & _
") AS cntVacant, " & _
"( " & _
"(select count(1) from tblStaff where (Title1 NOT LIKE '%Librarian%') and (Title1 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title2 NOT LIKE '%Librarian%') and (Title2 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title3 NOT LIKE '%Librarian%') and (Title3 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title4 NOT LIKE '%Librarian%') and (Title4 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title5 NOT LIKE '%Librarian%') and (Title5 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title6 NOT LIKE '%Librarian%') and (Title6 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title7 NOT LIKE '%Librarian%') and (Title7 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title8 NOT LIKE '%Librarian%') and (Title8 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title9 NOT LIKE '%Librarian%') and (Title9 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) + " & _
"(select count(1) from tblStaff where (Title10 NOT LIKE '%Librarian%') and (Title10 NOT LIKE '%Tech%') and ChartID=t_staff.ChartID) " & _
") AS cntOther " & _
"FROM tblGeneral INNER JOIN tblStaff t_staff ON tblGeneral.ChartID= t_staff.ChartID "
If Request.querystring("sort") = "" then
sql2 = sql2 & "ORDER BY City"
Else
sql2 = sql2 & "ORDER BY " & Request.querystring("sort")
End If
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open sql2, objConn
'------------------------------------------
'COUNT NUMBER OF FACILITIES REPORTING DATA
'------------------------------------------
sqlCountChartID = "SELECT COUNT(tblStaff.ChartID) AS CountChartID " & _
"FROM tblStaff "
Set objCountChartID = Server.CreateObject("ADODB.Recordset")
objCountChartID.Open sqlCountChartID, objConn
'------------------------------------------
'CALCULATIONS FOR FTE
'------------------------------------------
sqlSumLibFTE = "SELECT Sum(tblStaff.LibFTE) AS SumLibFTE " & _
"FROM tblStaff "
Set objSumLibFTE = Server.CreateObject("ADODB.Recordset")
objSumLibFTE.Open sqlSumLibFTE, objConn
'The following includes the records with 0 responses and then calculates an average
sqlAvgLibFTE = "SELECT SUM(LibFTE)/COUNT(LibFTE) AS AvgLibFTE " & _
"FROM tblStaff "
Set objAvgLibFTE = Server.CreateObject("ADODB.Recordset")
objAvgLibFTE.Open sqlAvgLibFTE, objConn
'------------------------------------------
'CALCULATION FOR OnBoardFTE
'------------------------------------------
sqlSumOnBoardFTE = "SELECT Sum(tblStaff.OnBoardFTE) AS SumOnBoardFTE " & _
"FROM tblStaff "
Set objSumOnBoardFTE = Server.CreateObject("ADODB.Recordset")
objSumOnBoardFTE.Open sqlSumOnBoardFTE, objConn
sqlAvgOnBoardFTE = "SELECT SUM(OnBoardFTE)/COUNT(OnBoardFTE) AS AvgOnBoardFTE " & _
"FROM tblStaff "
Set objAvgOnBoardFTE = Server.CreateObject("ADODB.Recordset")
objAvgOnBoardFTE.Open sqlAvgOnBoardFTE, objConn
'--------------------------------
'Percentage of FILLED positions
'--------------------------------
sqlFilled = "SELECT SUM(OnBoardFTE) / SUM(LibFTE) * 100 AS Filled " & _
"FROM tblStaff "
Set objFilled = Server.CreateObject("ADODB.Recordset")
objFilled.Open sqlFilled, objConn
'--------------------------------
'No of VACANCIES
'--------------------------------
sqlVacancies = "SELECT SUM(LibFTE) - SUM(OnBoardFTE) AS Vacancies " & _
"FROM tblStaff "
Set objVacancies = Server.CreateObject("ADODB.Recordset")
objVacancies.Open sqlVacancies, objConn
if err.number <> 0 then
response.write err.description
end if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta http-equiv="Subject" content="Library Orientation Evaluation">
<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">
<title><%=title%></title>
<link rel=stylesheet type="text/css"
href="/VALNET/CodeReuse/style.css">
</head>
<body>
<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>
<%
Response.Write "<ul>"
Response.Write "<li><font face='Arial' font size=2'>Total number of facilities reporting data: "
Response.write (objCountChartID("CountChartID"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size=2'>TOTAL Approved FTE Staffing: "
Response.write Formatnumber(objSumLibFTE("SumLibFTE"),2)
Response.write "<i> (" & Formatnumber(objFilled("Filled"),0) & "% filled)</i>"
Response.Write "<li><font face='Arial' font size=2'>Average Approved FTE Staffing Level: "
Response.write Formatnumber(objAvgLibFTE("AvgLibFTE"),2)
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size=2'>TOTAL Actual (on-board) FTE Staffing: "
Response.write Formatnumber(objSumOnBoardFTE("SumOnBoardFTE"),2)
Response.Write "<li><font face='Arial' font size=2'>Average Actual (on-board) FTE Staffing Level: "
Response.write Formatnumber(objAvgOnBoardFTE("AvgOnBoardFTE"),2)
Response.Write "<li><font face='Arial' font size=2'>TOTAL number of Vacancies: "
Response.write Formatnumber(objVacancies("Vacancies"),2)
Response.Write "</li></ul>"
Response.Write "<hr>"
%>
* Indicates a consolidated facility, no separate complexity level assigned.<br>
# A count of the number of positions in this category (not FTE)
<%
'First, display the TABLE header info:
Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='2' width='1000px'>"
'Display table headings for each column in the Recordset
Response.Write "<TR style='position:absolute;top:264px;background-color:white;'>"
Response.Write "<TH class='CCTable' scope='col' width='25px'> </th>"
Response.Write "<TH class='CCTable' scope='col' width='172px'><a href='StaffingPositions.asp?sort=City'><font size='1' color='blue'><b>Location</b></a></font></th></th>"
Response.Write "<TH class='CCTable' scope='col' width='85px'><a href='StaffingPositions.asp?sort=Facility'><font size='1' color='blue'><b>Facility</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='50px'><a href='StaffingPositions.asp?sort=VISNID'><font size='1' color='blue'><b>VISN</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=Complexity'><font size='1' color='blue'><b>Complexity</a> *</b><br><a target='_blank' href='/Valnet/ComparisonChart/ComplexityLevels.htm'><font size='1'>What is this?</a></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='70px'><a href='StaffingPositions.asp?sort=LibFTE'><font size='1' color='blue'><b>Approved<br>Library<br>FTE</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='72px'><font size='1' color='blue'><b>No. of<br>Librarian<br>Positions #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>FTE<br>Tech<br>Positions<br>(Filled)</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>All Other<br>Titles #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><a href='StaffingPositions.asp?sort=OnBoardFTE'><font size='1' color='blue'><b>Actual<br>Library<br>FTE</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='75px'><font size='1' color='blue'><b>No. of<br>Vacancies #</b></font></th>"
Response.Write "<TH class='CCTable' scope='col' width='250px'><font size='1' color='blue'><b>General<br>Staff<br>Notes</b></font></th></tr></TABLE>"
Response.Write "<div style='OVERFLOW:auto; HEIGHT:400px; position:absolute;top:328px;width:1025px'>"
Response.Write "<TABLE class='CCTable' cellspacing='0' border='1' cellpadding='0' width='1000px'>"
While Not objRS2.EOF
counter = counter + 1
Response.Write "<TR><TD class='CCTable' scope='row' width='25px'>" & counter
Response.Write "<TD class='CCTable' scope='row' width='148px'>" & objRS2("City") & ", " & objRS2("State") & "<font color='#FF0000'><b>" & "</FONT></B></TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("Facility") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='50px'>" & objRS2("VISNID") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='80px'>" & objRS2("Complexity") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("LibFTE") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntLibrary") & "</TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("SUMTechsFilled") & "</TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntOther") & "</TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("OnBoardFTE") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='75px'>" & objRS2("cntVacant") & " </TD>" & VbCrLf
Response.Write "<TD class='CCTable' scope='row' width='218px'>" & objRS2("StaffGenNotes") & " </TD>" & VbCrLf
'Advance the recordset
objRS2.MoveNext
Wend
objRS2.Close
Set objRS2 = Nothing
Response.Write "</td></TABLE>" & vbCrLf
Response.Write "</div>"
%>
</body>
</html>
ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
select
sum(case when Title like '%tech%' and PositionStatus like '%filled%' then Field1 + Field2 else 0 end)
from MyComplexQuery
Obviously the condition can be improved as can the fields to be summed.
Hope that gives you a starting point.
Of course it also depends on where you are using SQL Server 2005 or Access as they are quite different.