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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE