SELECT (Count(CountVacancies1) + Count(CountVacancies2) + Count(CountVacancies3) + Count(CountVacancies4) + Count(CountVacancies5) + Count(CountVacancies6) + Count(CountVacancies7) + Count(CountVacancies8) + Count(CountVacancies9) + Count(CountVacancies10)) AS SumVacancies
SELECT CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10
FROM (SELECT SUM(CASE
WHEN PositionStatus1 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies1,
SUM(CASE
WHEN PositionStatus2 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies2,
SUM(CASE
WHEN PositionStatus3 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies3,
SUM(CASE
WHEN PositionStatus4 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies4,
SUM(CASE
WHEN PositionStatus5 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies5,
SUM(CASE
WHEN PositionStatus6 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies6,
SUM(CASE
WHEN PositionStatus7 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies7,
SUM(CASE
WHEN PositionStatus8 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies8,
SUM(CASE
WHEN PositionStatus9 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies9,
SUM(CASE
WHEN PositionStatus10 = 'Vacant' THEN 1
ELSE 0
END) CountVacancies10
FROM tblStaff) t1
sqlCountVacancies = "SELECT Count(case PositionStatus1 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus2 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus3 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus4 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus5 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus6 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus7 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus8 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus9 when 'Vacant' then 1 else 0 end) " & _
"+ Count(case PositionStatus10 when 'Vacant' then 1 else 0 end) " & _
"FROM tblStaff "
or
sqlCountVacancies = "SELECT Count(*) " & _
"FROM tblStaff " & _
" where 'Vacant' in (positionstatus1,positionstatus2,positionstatus3,positionstatus4" & _
" ,positionstatus5,positionstatus6,positionstatus7,positionstatus8,positionstatus9,positionstatus10)"
'Count the TOTAL NUMBER OF VACANCIES
sqlCountVacancies = "SELECT Count(*) AS CountVacancies " & _
"FROM tblStaff " & _
"WHERE 'Vacant' in (positionstatus1,positionstatus2,positionstatus3,positionstatus4,positionstatus5,positionstatus6,positionstatus7,positionstatus8,positionstatus9,positionstatus10) "
Set objCountVacancies = Server.CreateObject("ADODB.Recordset")
objCountVacancies.Open sqlCountVacancies, objConn
'Count the TOTAL NUMBER OF VACANCIES
sql CountVacanciesTotal = "SELECT CountVacancies1, " & _
"CountVacancies2, " & _
"CountVacancies3, " & _
"CountVacancies4, " & _
"CountVacancies5, " & _
"CountVacancies6, " & _
"CountVacancies7, " & _
"CountVacancies8, " & _
"CountVacancies9, " & _
"CountVacancies10, " & _
"CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " _ &
"SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ &
"SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ &
"SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ &
"SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ &
"SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ &
"SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ &
"SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ &
"SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ &
"SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ &
"FROM tblStaff) "
<%
'Create recordset
Dim sql, objRS
sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
"tblGeneral.Notation, tblGeneral.ConsolidationNote, tblGeneral.AllHCF, " & _
"tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, " & _
"tblStaff.PositionStatus4, tblStaff.PositionStatus5, tblStaff.PositionStatus6, " & _
"tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, " & _
"tblStaff.PositionStatus10, tblStaff.LibFTE, " & _
"tblStaff.LibManTitle, tblStaff.LibManName, tblStaff.Title1, tblStaff.StaffGenNotes " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID "
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql, objConn
'------------------------------------------
'COUNTS
'------------------------------------------
'Count the number of all HCF facilities
sqlCountAllHCF = "SELECT COUNT(tblGeneral.AllHCF) AS CountAllHCF " & _
"FROM tblGeneral " & _
"WHERE tblGeneral.AllHCF = 'Yes' "
Set objCountAllHCF = Server.CreateObject("ADODB.Recordset")
objCountAllHCF.Open sqlCountAllHCF, objConn
'Count the FTE Total
sqlCountLibFTE = "SELECT Count(tblStaff.LibFTE) AS CountLibFTE " & _
"FROM tblStaff "
Set objCountLibFTE = Server.CreateObject("ADODB.Recordset")
objCountLibFTE.Open sqlCountLibFTE, objConn
'Count the number of POSITION #1 VACANCIES
sqlCountVacancies1 = "SELECT Count(tblStaff.PositionStatus1) AS CountVacancies1 " & _
"FROM tblStaff " & _
"WHERE tblStaff.PositionStatus1 = 'Vacant' "
Set objCountVacancies1 = Server.CreateObject("ADODB.Recordset")
objCountVacancies1.Open sqlCountVacancies1, objConn
'Count the number of POSITION #2 VACANCIES
sqlCountVacancies2 = "SELECT Count(tblStaff.PositionStatus2) AS CountVacancies2 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus2 = 'Vacant' "
Set objCountVacancies2 = Server.CreateObject("ADODB.Recordset")
objCountVacancies2.Open sqlCountVacancies2, objConn
'Count the number of POSITION #3 VACANCIES
sqlCountVacancies3 = "SELECT Count(tblStaff.PositionStatus3) AS CountVacancies3 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus3 = 'Vacant' "
Set objCountVacancies3 = Server.CreateObject("ADODB.Recordset")
objCountVacancies3.Open sqlCountVacancies3, objConn
'Count the number of POSITION #4 VACANCIES
sqlCountVacancies4 = "SELECT Count(tblStaff.PositionStatus4) AS CountVacancies4 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus4 = 'Vacant' "
Set objCountVacancies4 = Server.CreateObject("ADODB.Recordset")
objCountVacancies4.Open sqlCountVacancies4, objConn
'Count the number of POSITION #5 VACANCIES
sqlCountVacancies5 = "SELECT Count(tblStaff.PositionStatus5) AS CountVacancies5 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus5 = 'Vacant' "
Set objCountVacancies5 = Server.CreateObject("ADODB.Recordset")
objCountVacancies5.Open sqlCountVacancies5, objConn
'Count the number of POSITION #6 VACANCIES
sqlCountVacancies6 = "SELECT Count(tblStaff.PositionStatus6) AS CountVacancies6 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus6 = 'Vacant' "
Set objCountVacancies6 = Server.CreateObject("ADODB.Recordset")
objCountVacancies6.Open sqlCountVacancies6, objConn
'Count the number of POSITION #7 VACANCIES
sqlCountVacancies7 = "SELECT Count(tblStaff.PositionStatus7) AS CountVacancies7 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus7 = 'Vacant' "
Set objCountVacancies7 = Server.CreateObject("ADODB.Recordset")
objCountVacancies7.Open sqlCountVacancies7, objConn
'Count the number of POSITION #8 VACANCIES
sqlCountVacancies8 = "SELECT Count(tblStaff.PositionStatus8) AS CountVacancies8 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus8 = 'Vacant' "
Set objCountVacancies8 = Server.CreateObject("ADODB.Recordset")
objCountVacancies8.Open sqlCountVacancies8, objConn
'Count the number of POSITION #9 VACANCIES
sqlCountVacancies9 = "SELECT Count(tblStaff.PositionStatus9) AS CountVacancies9 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus9 = 'Vacant' "
Set objCountVacancies9 = Server.CreateObject("ADODB.Recordset")
objCountVacancies9.Open sqlCountVacancies9, objConn
'Count the number of POSITION #10 VACANCIES
sqlCountVacancies10 = "SELECT Count(tblStaff.PositionStatus10) AS CountVacancies10 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus10 = 'Vacant' "
Set objCountVacancies10 = Server.CreateObject("ADODB.Recordset")
objCountVacancies10.Open sqlCountVacancies10, objConn
'Count the TOTAL NUMBER OF VACANCIES
sqlCountVacancies = "SELECT Count(*) AS CountVacancies " & _
"FROM tblStaff " & _
"WHERE 'Vacant' IN (PositionStatus1,PositionStatus2,PositionStatus3,PositionStatus4,PositionStatus5,PositionStatus6,PositionStatus7,PositionStatus8,PositionStatus9,PositionStatus10) "
Set objCountVacancies = Server.CreateObject("ADODB.Recordset")
objCountVacancies.Open sqlCountVacancies, objConn
<%
'Create recordset
Dim sql, objRS
sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
"tblGeneral.Notation, tblGeneral.ConsolidationNote, tblGeneral.AllHCF, " & _
"tblStaff.PositionStatus1, tblStaff.PositionStatus2, tblStaff.PositionStatus3, " & _
"tblStaff.PositionStatus4, tblStaff.PositionStatus5, tblStaff.PositionStatus6, " & _
"tblStaff.PositionStatus7, tblStaff.PositionStatus8, tblStaff.PositionStatus9, " & _
"tblStaff.PositionStatus10, tblStaff.LibFTE, " & _
"tblStaff.LibManTitle, tblStaff.LibManName, tblStaff.Title1, tblStaff.StaffGenNotes " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus1='Vacant' OR tblStaff.PositionStatus2='Vacant' " & _
"OR tblStaff.PositionStatus3='Vacant' OR tblStaff.PositionStatus4='Vacant' " & _
"OR tblStaff.PositionStatus5='Vacant' OR tblStaff.PositionStatus6='Vacant' " & _
"OR tblStaff.PositionStatus7='Vacant' OR tblStaff.PositionStatus8='Vacant' " & _
"OR tblStaff.PositionStatus9='Vacant' OR tblStaff.PositionStatus10='Vacant'"
If Request.querystring("sort") = "" then
sql = sql & "ORDER BY City, State"
Else
sql = sql & "ORDER BY " & Request.querystring("sort")
End If
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql, objConn
'------------------------------------------
'COUNTS
'------------------------------------------
'Count the number of all HCF facilities
sqlCountAllHCF = "SELECT COUNT(tblGeneral.AllHCF) AS CountAllHCF " & _
"FROM tblGeneral " & _
"WHERE tblGeneral.AllHCF = 'Yes' "
Set objCountAllHCF = Server.CreateObject("ADODB.Recordset")
objCountAllHCF.Open sqlCountAllHCF, objConn
'Count the FTE Total
sqlCountLibFTE = "SELECT Count(tblStaff.LibFTE) AS CountLibFTE " & _
"FROM tblStaff "
Set objCountLibFTE = Server.CreateObject("ADODB.Recordset")
objCountLibFTE.Open sqlCountLibFTE, objConn
'Count the number of POSITION #1 VACANCIES
sqlCountVacancies1 = "SELECT Count(tblStaff.PositionStatus1) AS CountVacancies1 " & _
"FROM tblStaff " & _
"WHERE tblStaff.PositionStatus1 = 'Vacant' "
Set objCountVacancies1 = Server.CreateObject("ADODB.Recordset")
objCountVacancies1.Open sqlCountVacancies1, objConn
'Count the number of POSITION #2 VACANCIES
sqlCountVacancies2 = "SELECT Count(tblStaff.PositionStatus2) AS CountVacancies2 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus2 = 'Vacant' "
Set objCountVacancies2 = Server.CreateObject("ADODB.Recordset")
objCountVacancies2.Open sqlCountVacancies2, objConn
'Count the number of POSITION #3 VACANCIES
sqlCountVacancies3 = "SELECT Count(tblStaff.PositionStatus3) AS CountVacancies3 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus3 = 'Vacant' "
Set objCountVacancies3 = Server.CreateObject("ADODB.Recordset")
objCountVacancies3.Open sqlCountVacancies3, objConn
'Count the number of POSITION #4 VACANCIES
sqlCountVacancies4 = "SELECT Count(tblStaff.PositionStatus4) AS CountVacancies4 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus4 = 'Vacant' "
Set objCountVacancies4 = Server.CreateObject("ADODB.Recordset")
objCountVacancies4.Open sqlCountVacancies4, objConn
'Count the number of POSITION #5 VACANCIES
sqlCountVacancies5 = "SELECT Count(tblStaff.PositionStatus5) AS CountVacancies5 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus5 = 'Vacant' "
Set objCountVacancies5 = Server.CreateObject("ADODB.Recordset")
objCountVacancies5.Open sqlCountVacancies5, objConn
'Count the number of POSITION #6 VACANCIES
sqlCountVacancies6 = "SELECT Count(tblStaff.PositionStatus6) AS CountVacancies6 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus6 = 'Vacant' "
Set objCountVacancies6 = Server.CreateObject("ADODB.Recordset")
objCountVacancies6.Open sqlCountVacancies6, objConn
'Count the number of POSITION #7 VACANCIES
sqlCountVacancies7 = "SELECT Count(tblStaff.PositionStatus7) AS CountVacancies7 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus7 = 'Vacant' "
Set objCountVacancies7 = Server.CreateObject("ADODB.Recordset")
objCountVacancies7.Open sqlCountVacancies7, objConn
'Count the number of POSITION #8 VACANCIES
sqlCountVacancies8 = "SELECT Count(tblStaff.PositionStatus8) AS CountVacancies8 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus8 = 'Vacant' "
Set objCountVacancies8 = Server.CreateObject("ADODB.Recordset")
objCountVacancies8.Open sqlCountVacancies8, objConn
'Count the number of POSITION #9 VACANCIES
sqlCountVacancies9 = "SELECT Count(tblStaff.PositionStatus9) AS CountVacancies9 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus9 = 'Vacant' "
Set objCountVacancies9 = Server.CreateObject("ADODB.Recordset")
objCountVacancies9.Open sqlCountVacancies9, objConn
'Count the number of POSITION #10 VACANCIES
sqlCountVacancies10 = "SELECT Count(tblStaff.PositionStatus10) AS CountVacancies10 " & _
"FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
"WHERE tblStaff.PositionStatus10 = 'Vacant' "
Set objCountVacancies10 = Server.CreateObject("ADODB.Recordset")
objCountVacancies10.Open sqlCountVacancies10, objConn
'Count the TOTAL NUMBER OF VACANCIES
sql CountVacanciesTotal = "SELECT CountVacancies1, " & _
"CountVacancies2, " & _
"CountVacancies3, " & _
"CountVacancies4, " & _
"CountVacancies5, " & _
"CountVacancies6, " & _
"CountVacancies7, " & _
"CountVacancies8, " & _
"CountVacancies9, " & _
"CountVacancies10, " & _
"CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, "
"SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ &
"SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ &
"SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ &
"SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ &
"SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ &
"SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ &
"SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ &
"SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ &
"SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ &
"FROM tblStaff) as CountVacanciesTotal "
Set objCountVacanciesTotal = Server.CreateObject("ADODB.Recordset")
objCountVacanciesTotal.Open sqlCountVacanciesTotal, objConn
Response.Write "<ul>"
Response.Write "<li><font face='Arial' font size=2'>Total number of facilities with Libraries (ALL HCF facilities): "
Response.write (objCountAllHCF("CountAllHCF"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Total FTE: "
Response.write (objCountLibFTE("CountLibFTE"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 1 vacancies: "
Response.write (objCountVacancies1("CountVacancies1"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 2 vacancies: "
Response.write (objCountVacancies2("CountVacancies2"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 3 vacancies: "
Response.write (objCountVacancies3("CountVacancies3"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 4 vacancies: "
Response.write (objCountVacancies4("CountVacancies4"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 5 vacancies: "
Response.write (objCountVacancies5("CountVacancies5"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 6 vacancies: "
Response.write (objCountVacancies6("CountVacancies6"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 7 vacancies: "
Response.write (objCountVacancies7("CountVacancies7"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 8 vacancies: "
Response.write (objCountVacancies8("CountVacancies8"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 9 vacancies: "
Response.write (objCountVacancies9("CountVacancies9"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Number of Position 10 vacancies: "
Response.write (objCountVacancies10("CountVacancies10"))
Response.Write "</li>"
Response.Write "<li><font face='Arial' font size='2'>Total vacancies: "
Response.write (objCountVacanciesTotal("CountVacanciesTotal"))
Response.Write "</li>"
Response.Write "</ul>"
sql CountVacanciesTotal = "SELECT " & _
"CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, "
"SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ &
"SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ &
"SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ &
"SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ &
"SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ &
"SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ &
"SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ &
"SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ &
"SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ &
"FROM tblStaff) as CountVacanciesTotal "
sql CountVacanciesTotal = "SELECT " & _
"CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10 AS TotalCount " & _
"FROM (SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " _ &
"SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " _ &
"SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " _ &
"SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " _ &
"SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " _ &
"SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " _ &
"SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " _ &
"SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " _ &
"SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " _ &
"SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10) " _ &
"FROM tblStaff) as CountVacanciesTotal "
sql CountVacanciesTotal = "SELECT " & _
"(CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10) AS TotalCount " & _
...rest of the query...
'Count the TOTAL NUMBER OF VACANCIES
sqlCountVacanciesTotal = "SELECT " & _
"(CountVacancies1 + CountVacancies2 + CountVacancies3 + CountVacancies4 + CountVacancies5 + CountVacancies6 + CountVacancies7 + CountVacancies8 + CountVacancies9 + CountVacancies10) AS TotalCount " & _
"FROM SELECT SUM(CASE WHEN PositionStatus1 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies1, " & _
"SUM(CASE WHEN PositionStatus2 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies2, " & _
"SUM(CASE WHEN PositionStatus3 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies3, " & _
"SUM(CASE WHEN PositionStatus4 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies4, " & _
"SUM(CASE WHEN PositionStatus5 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies5, " & _
"SUM(CASE WHEN PositionStatus6 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies6, " & _
"SUM(CASE WHEN PositionStatus7 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies7, " & _
"SUM(CASE WHEN PositionStatus8 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies8, " & _
"SUM(CASE WHEN PositionStatus9 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies9, " & _
"SUM(CASE WHEN PositionStatus10 = 'Vacant' THEN 1 ELSE 0 END) CountVacancies10 " & _
"FROM tblStaff) as CountVacanciesTotal "
Set objCountVacanciesTotal = Server.CreateObject("ADODB.Recordset")
objCountVacanciesTotal.Open sqlCountVacanciesTotal, objConn
Select (temp1.p1 + temp2.p2 + ... + temp10.p10) as Total
from
(SELECT count(*) as p1
FROM tblStaff
WHERE PositionStatus1='VACANT') as temp1,
(SELECT count(*) as p2
FROM tblStaff
WHERE PositionStatus2='VACANT') as temp2,
...
(SELECT count(*) as p10
FROM tblStaff
WHERE PositionStatus10='VACANT') as temp10
Open in new window
This will return the sum of the count of all the different possible values of tblStaff.PositionStatus1.
You could add a where clause(in between the From and Group by clause for all specific tblStaff.PositionStatus1's
Open in new window
and so on