I have a scenario where I have multiple occurrences of Emp in a table. I want to return a string Context depending on these conditions:

If for one Emp

• Dep_no in (10,11,12) only => 'FTB'
• Dep_no in (22,23) only => 'SD'
• Dep_no in (10,11,12) and in (22,23) => 'IT'

The 3rd condition can match, because Dep_no can be in these two sets in different records for the same Emp.

How can I implement the third case using a case statement or any other solution in SQL server 2016?

For example an Employee has Dep_No in 10 and 22 (yellow example), he should be IT

Employee has 10 and/or 11 he should be FTB

Employee has 22 and/or 23 then he should be SD

Data in table look like below

CodePudding user response：

CREATE TABLE Thing (
    Emp    int NOT NULL,
    Dep_No int NOT NULL
)

INSERT INTO Thing (Emp, Dep_No)
VALUES 
    (123, 10),
    (123, 11),
    (455, 22),
    (455, 23),
    (566, 10),
    (566, 22)

SELECT e.Emp,
    CASE 
        WHEN ftb.Emp IS NOT NULL AND sd.Emp IS NOT NULL THEN 'IT'
        WHEN ftp.Emp IS NOT NULL AND sd.Emp IS     NULL THEN 'FTB'
        WHEN ftb.Emp IS     NULL AND sd.Emp IS NOT NULL THEN 'SD'
        ELSE '?'
    END AS NewThing
FROM (
    -- PK of the result table.
    SELECT DISTINCT Emp FROM Thing
) AS e
    LEFT JOIN (
        -- Lookup case FTB.
        SELECT DISTINCT Emp FROM Thing WHERE Dep_No IN (10, 11, 12)
    ) AS ftb
        ON e.Emp = ftb.Emp
    LEFT JOIN (
        -- Lookup case SD.
        SELECT DISTINCT Emp FROM Thing WHERE Dep_No IN (22, 23)
    ) AS sd
        ON e.Emp = sd.Emp

CodePudding user response：

You can solve it with nested SELECTs. The inner SELECT counts the number of times a Dep_no is in one the two sets of numbers. The outer SELECT then returns the desired string based on these counts.

SELECT
   Emp,
   CASE WHEN g1>0 AND g2>0 THEN 'IT'
        WHEN g1>0 THEN 'FTB'
        WHEN g2>0 THEN 'SD'
   END AS Context
FROM (
   SELECT
      Emp,
      SUM(CASE WHEN Dep_No IN (10, 11, 12) THEN 1 ELSE 0 END) AS g1,
      SUM(CASE WHEN Dep_No IN (22, 23) THEN 1 ELSE 0 END) AS g2
   FROM myTable
   GROUP BY Emp
) g

With this example table

create table myTable (
  Emp int,
  Dep_No int
);
insert into MyTable (Emp, Dep_No) VALUES (123,10), (123,11), (455, 22), (455, 23), (566, 10), (566, 22);

We get this result

See: http://sqlfiddle.com/#!18/339814/3/0