This is the query that took forever before it was changed.

$employeeQuery = "
    SELECT TOP " . ($displayRows + 1) . " MAX(t.TrainingDate) as LastTrained, neededTraining.*
    FROM TrainingEvents t RIGHT JOIN (
        SELECT *
        FROM (
            --What they are supposed to have
            SELECT
              E.ID as E_ID,
              E.LastName + ', ' + E.FirstName AS Name,
              l.LocationDescription,
              s.ShiftDescription,
              wi.Revision,
              wi.ID AS WI_ID,
              wi.FormNumber,
              wi.Title AS WorkTitle,
              wi.JobCodeID,
              wi.ExternalTraining
            FROM Employees E inner join JobCodeEmployeeLink jcel ON E.ID = jcel.PersonID
                             inner join JobCodes jc ON jcel.JobCodeID= jc.ID
                             inner join WorkInstructions wi ON jc.ID = wi.JobCodeID
                     inner join Location l ON l.ID = E.LocationID
                     inner join [Shift] s ON s.ID = E.ShiftID
            WHERE (YEAR(E.HireDate) >= 2013
              OR (YEAR(wi.LastUpdated) >= 2013))
              AND (E.Active = 1)";
              if ($_SESSION["userType"] != "Admin") {
                $employeeQuery .= "
              AND E.LocationID = " . $_SESSION['location'];
              }
              if ($_SESSION["userType"] == "Supervisor") {
                $employeeQuery .= "
              AND E.ShiftID = " . $_SESSION['shift'];
              }
              $employeeQuery .= "
            EXCEPT
            --What they have
            SELECT
              e.ID as E_ID,
              e.LastName + ', ' + e.FirstName AS Name,
              l.LocationDescription,
              s.ShiftDescription,
              t.RevisionNumber as Revision,
              w.ID as WI_ID,
              w.FormNumber,
              w.Title AS WorkTitle,
              w.JobCodeID,
              w.ExternalTraining
            FROM Employees e, TrainingEvents t, WorkInstructions w, Location l, [Shift] s
            WHERE e.ID=t.PersonID AND t.WorkInstructionID = w.ID AND e.LocationID=l.ID) 
        AS neededTrainingSubset) neededTraining 
    ON t.PersonID=neededTraining.E_ID AND t.WorkInstructionID=neededTraining.WI_ID AND t.RevisionNumber < neededTraining.Revision
    GROUP BY neededTraining.E_ID, 
      neededTraining.FormNumber, 
      neededTraining.JobCodeID, 
      neededTraining.LocationDescription, 
      neededTraining.Name,
      neededTraining.Revision,
      neededTraining.ShiftDescription,
      neededTraining.WI_ID,
      neededTraining.WorkTitle,
      neededTraining.ExternalTraining
      ;";

DatabaseManagementSystems/NastyQuery (last edited 2014-12-03 17:38:25 by scot)