Problem: I'm trying to see, per classID, if an employee (empID) has at least 1 StateCertArea in the FedCertArea, and flag = 1 if so.
Background knowledge on this fake case:
I need to avoid a SELECT in a CASE statement if possible. Maybe using some temp tables and a join, but I'll take the help I can get.
DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable ( ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))
INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')
SELECT t.ClassID
, t.EmpID
, t.StateCertArea
, t.FedCertArea
--, CASE WHEN 'EmployeeID has at least 1 StateCertArea in FedCertArea' THEN 1 ELSE 0 END AS IsInArea
FROM #temptable AS t
Expected Result:
An outer apply
provides an efficient way to run correlated subqueries such as the one needed here e.g:
DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable (ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))
INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')
SELECT DISTINCT
t.ClassID
, t.EmpID
, CASE WHEN oa.HasMatchingArea = 1 THEN 1 ELSE 0 END AS IsInArea
FROM #temptable AS t
OUTER APPLY (
SELECT TOP(1) 1 AS HasMatchingArea
FROM #temptable AS t2
WHERE t2.ClassID = t.ClassID
AND t2.EmpID = t.EmpID
AND t2.StateCertArea = t.FedCertArea
) AS oa
ClassID | EmpID | IsInArea |
---|---|---|
222555 | 77777 | 0 |
572888 | 77777 | 1 |
alternate using coalesce
SELECT DISTINCT
t.ClassID
, t.EmpID
, COALESCE(oa.HasMatchingArea,0) AS IsInArea
FROM #temptable AS t
OUTER APPLY (
SELECT TOP(1) 1 AS HasMatchingArea
FROM #temptable AS t2
WHERE t2.ClassID = t.ClassID
AND t2.EmpID = t.EmpID
AND t2.StateCertArea = t.FedCertArea
) AS oa