Deeply embedded subqueries, Sub-query hell

1. Deeply embedded sql query (Inventory management for KidProof Canada)

SELECT TeacherInventory.TeacherInventoryID, [User].LastName + ', ' + [User].FirstName AS TeacherName, Material.MaterialID, 
	Material.MaterialName, TeacherInventory.Remaining, g.Needed, g.Estimate 
FROM Material 
  INNER JOIN TeacherInventory ON Material.MaterialID = TeacherInventory.MaterialID 
  INNER JOIN [User] ON TeacherInventory.UserID = [User].UserID 
  Left outer join
    (SELECT t1.MaterialID, SUM(t1.ReqNum) as Needed 
-- total material for a matid, needed by all courses of a teacher in a time
       , sum(t1.Esti) as Estimate
    FROM (SELECT CourseMaterials.MaterialID, -- each course req matid will produce one line 
                  (CASE CourseMaterials.Extra 
                       WHEN 0 THEN Course.OccupiedSeats 
                       ELSE (Case Course.Private 
                                    when 1 then 0 
                                   else (SELECT SUM(CAST(StudentCourse.RequireExtraMaterial AS int)) 
                                           FROM StudentCourse 
    WHERE (StudentCourse.CourseID = Course.CourseID) 
                                                 AND (StudentCourse.Deleted = 0)) end) 
                    END) AS ReqNum
                  ,(CASE CourseMaterials.Extra WHEN 0 THEN Course.MaxSeats 
                       ELSE Course.MaxSeats * 0.7 * ~Course.Private END) AS Esti
    FROM Course 
             INNER JOIN CourseTeachers ON Course.CourseID = CourseTeachers.CourseID 
             INNER JOIN CourseMaterials ON Course.CourseTypeID = CourseMaterials.CourseTypeID 
    WHERE (Course.StartDate > GETDATE()) AND 
             (Course.StartDate <= DATEADD(dd,@FutureDays, GETDATE())) 
             AND (Course.AuditDone = 0) and (Course.Waiting = 0) 
             AND (CourseTeachers.UserID = @TeacherID) 
             AND (CourseTeachers.Priority = 1)) t1
     Group by t1.MaterialID
-- t1 is the material needs for un-audited classes of a teacher in a time frame
     ) g on g.MaterialID= Material.MaterialID
WHERE (TeacherInventory.UserID = @TeacherID) 
-- for materials not in inventory of the teacher yet
UNION ALL 
SELECT NULL AS TeacherInventoryID, 
  (SELECT [User].LastName + ', ' + [User].FirstName AS TeacherName 
    FROM [USER] WHERE [USER].UserID = @TeacherID) AS TeacherName, 
  t.MaterialID, 
  (SELECT Material.MaterialName 
    FROM Material WHERE Material.MaterialID = t.MaterialID) AS MaterialName, 
  NULL AS Remaining, SUM(t.ReqNum) AS Needed, Sum(t.Esti) as Estimate
FROM (SELECT CourseMaterials.MaterialID, 
              (CASE CourseMaterials.Extra 
                    WHEN 0 THEN Course.OccupiedSeats 
                     ELSE (Case Course.Private 
                                when 1 then 0 
                                else (SELECT SUM(CAST(StudentCourse.RequireExtraMaterial AS int)) 
                                            FROM StudentCourse 
    WHERE (StudentCourse.CourseID = Course.CourseID) 
                                                 AND (StudentCourse.Deleted = 0)) end) 
                    END) AS ReqNum
                  ,(CASE CourseMaterials.Extra WHEN 0 THEN Course.MaxSeats 
                       ELSE Course.MaxSeats * 0.7 * ~Course.Private END) AS Esti
    FROM Course INNER JOIN CourseTeachers ON Course.CourseID = CourseTeachers.CourseID 
      INNER JOIN CourseMaterials ON Course.CourseTypeID = CourseMaterials.CourseTypeID 
    WHERE (Course.StartDate > GETDATE()) 
        AND (Course.StartDate <= DATEADD(dd, @FutureDays, GETDATE())) 
        AND (Course.AuditDone = 0) and (Course.Waiting = 0) 
        AND (CourseTeachers.UserID = @TeacherID) AND (CourseTeachers.Priority = 1) 
        AND CourseMaterials.MaterialID 
          NOT IN 
          (SELECT Material.MaterialID 
    FROM Material INNER JOIN TeacherInventory 
             ON Material.MaterialID = TeacherInventory.MaterialID 
    WHERE (TeacherInventory.UserID = @TeacherID))) t 
-- t is a join list of (material, (class,class need)), restricted by user, not audited, time
-- frame, and material not in inventory yet
GROUP BY t.MaterialID
go

　

2. Some stored procedures for JobController

-- assume @search is not empty
-- if @search is empty, please use other stored procedures
-- compnayid and contactid can be 0
-- if companyid is 0, contactid is ignored
CREATE PROCEDURE dbo.SearchJobs(@companyID int, @contactID int, 
  @search nvarchar(50), @employeeID int, @QALevel int, @IncludeArchived bit)
AS Begin
if @CompanyID<> 0 and @contactID<> 0
begin 
  if @IncludeArchived = 1 
   if exists(select top 1 CompanyContactID from dbo.EmployeeSpecialCompanyLink 
    where CompanyContactID = @contactID and EmployeeID =@employeeID )
    SELECT Jobs.JobsID AS ID, 
           cast(DateCreated as smalldatetime) as [Date], LastName + N',' + FirstName AS Name, 
           BackCheckFileNumber AS  File#, 
           RushJob AS Rush, dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck) 
           AS [Can Criminal], 
           RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
           EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead,  Complete, 
           (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end) 
              as  ReferenceCheckStatus,
           (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) 
            end) as EducationVerificationStatus, 1 as IsSpecial
    FROM 
           dbo.Employee EduEmp INNER JOIN
           dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID 
           RIGHT OUTER JOIN
           dbo.Employee RefEmp INNER JOIN
           dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
           RIGHT OUTER JOIN dbo.Jobs 
           ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where CompanyID = @companyID and CompanyContactID = @contactID and 
           ISNULL(Jobs.Status,0)=0 and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) 
           or (BackCheckFileNumber like @search ) or 
           dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
           or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
        ORDER BY convert(nchar(10),DateCreated,102),3,1
      else
    SELECT Jobs.JobsID AS ID, 
             cast(DateCreated as smalldatetime) as [Date], LastName + N',' + FirstName AS Name, 
             BackCheckFileNumber AS File#,  RushJob AS Rush, 
             dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck) AS 
             [Can Criminal], 
             RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
             EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead, Complete, 
             (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end) as 
             ReferenceCheckStatus, 
            (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) 
            end) as EducationVerificationStatus,
            0 as IsSpecial
    FROM 
           dbo.Employee EduEmp INNER JOIN
           dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID 
           RIGHT OUTER JOIN
           dbo.Employee RefEmp INNER JOIN 
           dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
           RIGHT OUTER JOIN dbo.Jobs 
           ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where CompanyID = @companyID and CompanyContactID = @contactID 
           and Jobs.QALevel <= @QALevel  and ISNULL(Jobs.Status,0)=0 
           and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) 
              or (BackCheckFileNumber like @search )
              or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
              or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 ) 
         ORDER BY convert(nchar(10),DateCreated,102),3,1)
  else
     if exists(select top 1 CompanyContactID from dbo.EmployeeSpecialCompanyLink where 
                      CompanyContactID = @contactID and EmployeeID =@employeeID )
    SELECT Jobs.JobsID AS ID,  cast(DateCreated as smalldatetime) as [Date], 
            LastName + N',' + FirstName AS Name, BackCheckFileNumber AS File#,  RushJob AS Rush, 
            dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck) AS 
             [Can Criminal],   
            RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
            EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead, Complete, 
            (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end) as 
               ReferenceCheckStatus,
            (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) 
             end) as EducationVerificationStatus, 1 as IsSpecial
    FROM 
           dbo.Employee EduEmp INNER JOIN
           dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID 
           RIGHT OUTER JOIN dbo.Employee RefEmp INNER JOIN 
           dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
           RIGHT OUTER JOIN dbo.Jobs  ON ReferenceCheck.JobsID = Jobs.JobsID 
           ON EducationVerification.JobsID = Jobs.JobsID
    where CompanyID = @companyID and CompanyContactID = @contactID and 
             ISNULL(Jobs.Status,0)=0 and isnull(Jobs.Archived,0)=0 and  
            ( (FirstName + N' ' + LastName + N',' + FirstName like @search) 
               or (BackCheckFileNumber like @search ) 
               or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
               or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
        ORDER BY convert(nchar(10),DateCreated,102),3,1
     else
    SELECT Jobs.JobsID AS ID, cast(DateCreated as smalldatetime) as [Date], LastName + N',' +  
            FirstName AS Name, BackCheckFileNumber AS File#,  RushJob AS Rush, 
            dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck) AS 
            [Can Criminal], RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
            EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead,  Complete, 
            (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end) as 
            ReferenceCheckStatus,
            (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) 
            end) as EducationVerificationStatus, 0 as IsSpecial
    FROM dbo.Employee EduEmp INNER JOIN
           dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID 
           RIGHT OUTER JOIN dbo.Employee RefEmp INNER JOIN
           dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
           RIGHT OUTER JOIN dbo.Jobs 
           ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where CompanyID = @companyID and CompanyContactID = @contactID and 
          Jobs.QALevel <= @QALevel and ISNULL(Jobs.Status,0)=0 and isnull(Jobs.Archived,0)=0
          and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) 
              or (BackCheckFileNumber like @search )
              or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
              or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
        ORDER BY convert(nchar(10),DateCreated,102),3,1
  return 0
end
SET NOCOUNT ON
create table #SearchJobsTmp ( ID int unique nonclustered, 
[Date] smalldatetime, [Name] nvarchar(100), File# nvarchar(50), Rush bit, 
[Can Criminal] nvarchar(20), [Can Credit] nvarchar(20), [US Criminal] nvarchar(20), [US Credit] nvarchar(20), Drivers nvarchar(20), [Database] nvarchar(20), 
Interview nvarchar(20), [Int Criminal] nvarchar(20), [Cred Verify] nvarchar(20), 
RefLead nvarchar(100), EduLead nvarchar(100),
Complete bit, ReferenceCheckStatus int, EducationVerificationStatus int, IsSpecial bit)
create table #SearchJobsTmp2 (ID int unique nonclustered, 
[Date] smalldatetime, [Name] nvarchar(100), File# nvarchar(50), Rush bit, 
[Can Criminal] nvarchar(20), [Can Credit] nvarchar(20), 
[US Criminal] nvarchar(20), [US Credit] nvarchar(20), 
Drivers nvarchar(20), [Database] nvarchar(20), 
Interview nvarchar(20), [Int Criminal] nvarchar(20), [Cred Verify] nvarchar(20), 
RefLead nvarchar(100), EduLead nvarchar(100),
Complete bit, ReferenceCheckStatus int, EducationVerificationStatus int, IsSpecial bit)
if @CompanyID<> 0 -- @contactID must be zero
begin 
  Insert into #SearchJobsTmp
  SELECT Jobs.JobsID, DateCreated, LastName + N',' + FirstName, BackCheckFileNumber, RushJob, 
      dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck),  
      RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
      EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead,
      Complete, 
      (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end),
      (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) 
      end), 1
    FROM 
     dbo.Employee EduEmp INNER JOIN
     dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID 
     RIGHT OUTER JOIN dbo.Employee RefEmp INNER JOIN
     dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
     RIGHT OUTER JOIN dbo.Jobs INNER JOIN
     dbo.EmployeeSpecialCompanyLink as link ON Jobs.CompanyContactID = link.CompanyContactID 
     ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where link.EmployeeID = @employeeID and link.CompanyID = @CompanyID 
     and ISNULL(Jobs.Status,0)=0 and (@IncludeArchived = 1 or isnull(Jobs.Archived,0)=0)
     and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) or 
          (BackCheckFileNumber like @search )
          or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
          or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
  order by convert(nchar(10),DateCreated,102),3,1
  Insert into #SearchJobsTmp2
    SELECT Jobs.JobsID, DateCreated, LastName + N',' + FirstName, BackCheckFileNumber, RushJob, 
      dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck), 
      RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
      EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead,
      Complete, 
      (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end),
      (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) 
      end), 0
    FROM 
    dbo.Employee EduEmp INNER JOIN
    dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID      
    RIGHT OUTER JOIN dbo.Employee RefEmp INNER JOIN
    dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
    RIGHT OUTER JOIN dbo.Jobs 
    ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where CompanyID = @CompanyID and Jobs.QALevel < = @QALevel and 
      ISNULL(Jobs.Status,0)= 0   and (@IncludeArchived = 1 or isnull(Jobs.Archived,0)=0)
     and NOT EXISTS (select 1 from #SearchJobsTmp where Jobs.JobsID = #SearchJobsTmp.[ID])
     and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) or (BackCheckFileNumber 
          like @search ) or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
          or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
  order by convert(nchar(10),DateCreated,102),3,1
-- order by dbo.Jobs.BackCheckFileNumber asc
end
else -- even if contactid is not zero, since companyid is zero, so we regard both be zero, so no company -- and contact were specified
begin
  Insert into #SearchJobsTmp
    SELECT Jobs.JobsID, DateCreated, LastName + N',' + FirstName, BackCheckFileNumber, RushJob, 
    dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck) , 
    RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
    EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead,
    Complete, 
    (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end),
    (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) end),
    1
    FROM 
    dbo.Employee EduEmp INNER JOIN
    dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID 
    RIGHT OUTER JOIN dbo.Employee RefEmp INNER JOIN
    dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
    RIGHT OUTER JOIN dbo.Jobs INNER JOIN
    dbo.EmployeeSpecialCompanyLink as link ON Jobs.CompanyContactID = link.CompanyContactID 
    ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where link.EmployeeID = @employeeID and ISNULL(Jobs.Status,0)=0 and (@IncludeArchived = 1 
      or isnull(Jobs.Archived,0)=0) and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) 
         or (BackCheckFileNumber like @search )
         or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
         or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
  order by convert(nchar(10),DateCreated,102),3,1
  Insert into #SearchJobsTmp2
    SELECT Jobs.JobsID, DateCreated, LastName + N',' + FirstName, BackCheckFileNumber, RushJob, 
    dbo.GetStatusName(CanadianCriminalCheckStatus,CanadianCriminalCheck),  
    RefEmp.EmployeeFirstName + N' ' + RefEmp.EmployeeLastName AS RefLead, 
    EduEmp.EmployeeFirstName + N' ' + EduEmp.EmployeeLastName AS EduLead,
    Complete, 
    (case when isnull(ReferenceCheck,0)=0 then null else isnull(ReferenceCheckStatus,0) end),
    (case when isnull(EducationVerification,0)=0 then null else isnull(EducationVerificationStatus,0) end),
    0
    FROM dbo.Employee EduEmp INNER JOIN
    dbo.EducationVerification ON EduEmp.EmployeeID = EducationVerification.FileLeadEmployeeID    
    RIGHT OUTER JOIN dbo.Employee RefEmp INNER JOIN
    dbo.ReferenceCheck ON RefEmp.EmployeeID = ReferenceCheck.FileLeadEmployeeID 
    RIGHT OUTER JOIN dbo.Jobs 
    ON ReferenceCheck.JobsID = Jobs.JobsID ON EducationVerification.JobsID = Jobs.JobsID
    where Jobs.QALevel <= @QALevel and ISNULL(Jobs.Status,0)= 0 and (@IncludeArchived= 1 or  
      isnull(Jobs.Archived,0)=0) and 
      NOT EXISTS (select 1 from #SearchJobsTmp where Jobs.JobsID = #SearchJobsTmp.[ID])
     and ( (FirstName + N' ' + LastName + N',' + FirstName like @search) or 
         (BackCheckFileNumber like @search )
         or dbo.JobHasEducationName(Jobs.JobsID, EducationVerification, @search)=1 
         or dbo.JobHasReferenceName(Jobs.JobsID, ReferenceCheck, @search)=1 )
  order by convert(nchar(10),DateCreated,102),3,1
-- order by dbo.Jobs.BackCheckFileNumber asc
end
SET NOCOUNT OFF
select * from #SearchJobsTmp
union all
select * from #SearchJobstmp2
END
GO