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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

-- 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 