sqlsql-servert-sqlsql-server-2019

How to compare multiple records in multiple rows for the same ID?


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:

enter image description here


Solution

  • 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

    fiddle

    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