Following is a simplified version of my database in SQL Server 2005. I need to select employees based on business units. Each employee has home department, parent department and visiting department. Based on the department, business unit can be found out.
- For an employee, if the HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID.
- If HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID.
Following query works fine. But it has scan on the #DepartmentBusinesses table two times. Is there a way to use the table #DepartmentBusinesses only once by making it as a CASE statement or similar?
DECLARE @SearchBusinessUnitCD CHAR(3)
SET @SearchBusinessUnitCD = 'B'
--IF HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID
--IF HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID
CREATE TABLE #DepartmentBusinesses (DeptID INT, BusinessUnitCD CHAR(3))
INSERT INTO #DepartmentBusinesses
SELECT 1, 'A' UNION ALL
SELECT 2, 'B'
CREATE NONCLUSTERED INDEX IX_DepartmentBusinesses_DeptIDBusinessUnitCD ON #DepartmentBusinesses (DeptID,BusinessUnitCD)
DECLARE @Employees TABLE (EmpID INT, HomeDeptID INT, ParentDeptID INT, VisitingDeptID INT)
INSERT INTO @Employees
SELECT 1, 1, 1, 2 UNION ALL
SELECT 2, 2, 1, 3
SELECT *
FROM @Employees
WHERE
(
HomeDeptID = ParentDeptID
AND
EXISTS (
SELECT 1
FROM #DepartmentBusinesses
WHERE DeptID = VisitingDeptID
AND BusinessUnitCD = @SearchBusinessUnitCD)
)
OR
(
HomeDeptID <> ParentDeptID
AND
EXISTS (
SELECT 1
FROM #DepartmentBusinesses
WHERE DeptID = ParentDeptID
AND BusinessUnitCD = @SearchBusinessUnitCD
)
)
DROP TABLE #DepartmentBusinesses
Plan
