In order to get EMP as an individual type value the businessunit must satisfy this part of the case expression:

when b.businessunit in ('PLCLM8-BI') 
        or b.businessunit in ( 'PI-FldSls2') 
        or b.businessunit in ('PI-HODst4' ) 
        or b.businessunit in ('EXTLV')
    THEN 'EMP'

DIST is not in this list.

As I understand your problem, if any of the business units for a given individual fall into the “EMP” group, then that individual is considered an “EMP”. Otherwise, if any of the business units for that individual fall into the “IA” or “CWK” groups, that individual is considered a “IA” or “CWK”, respectfully.

Your logic does not address the possibility of an individual having both “IA” and “CWK” business units, but I’ll assume that “IA” supersedes “CWK”.

To get your desired results, you need to:

1. Map all of the business units to individual types. Although this can be done with an embedded case expression, it would be much better to have a some kind of lookup table that defines this mapping. This reduces code clutter and should improve maintainability.
2. For each individual, we need to scan all of the business units and mapped individual types for that individual and pick the select the top preferred individual type for whatever mix we might have. This can be done with a combination of a case expression, window functions, and conditional aggregation.

The business unit to type mapping would be something like:

CREATE TABLE businessunittype (
    businessunit VARCHAR(50),
    businessunittype VARCHAR(50)
)

INSERT INTO businessunittype
VALUES
    ('DIST',       'IA'),
    ('CS-IA',      'IA'),
    ('GS-IA',      'IA'),
    ('CI-CLMS',    'EMP'),
    ...,
    ('EXTLV',      'EMP'),
    ('PI-IA',      'CWK'),
    ('CI-Clms',    'CWK'),
    ('PI-temp',    'CWK')

The query would be something like:

select
    i.lastname, i.firstname, i.empnumber, b.businessunit, bt.businessunittype,
    case
        when count(case when bt.businessunittype = 'EMP' THEN 1 END)
             over(partition by i.empnumber) > 0 then 'EMP'  -- Point 2 
        when count(case when bt.businessunittype = 'IA' THEN 1 END)
             over(partition by i.empnumber) > 0 then 'IA'   -- Point 2
        when count(case when bt.businessunittype = 'CWK' THEN 1 END)
             over(partition by i.empnumber) > 0 then 'CWK'  -- Point 3
        else 'Other'
        end AS individualtype
from individual i
join businessunit b
    on b.internalid = i.internalid
left join businessunittype bt
    on bt.businessunit = b.businessunit
order by i.empnumber, b.internalid

There are likely other ways this logic could be refactored, such as wrapping the individualtype logic up in a CTE or subquery and then joining it with other data in the final select. The businessunittype table could have other forms, such as having flag bits instead of a text field to indicate the mapped type.

Results (with some missing business unit mappings applied and extra test data):

See this db<>fiddle for a demo.

I think you absolutely “need” a mapping table that holds the type for each emp number, to avoid horrible, extremely complex queries. Then in future you just need to fill this table. I would make this table as simple as possible and use a trivial JOIN.

So the table could for example be like this:

CREATE TABLE individual_types 
(empnumber varchar(100),
individualtype varchar(100),
description varchar(100));

The description column is optional, you could save there an information what EMP, IA etc. means. You could also add a foreign key to the emp number if you want.

Then your query will be quite simple and produce the expected result:

SELECT 
  i.lastname,
  i.firstname,
  i.empnumber, 
  b.businessunit,
  t.individualtype
FROM
  individual i LEFT JOIN businessunit b
    ON i.internalid = b.internalid
  INNER JOIN individual_types t
    ON i.empnumber = t.empnumber
  WHERE 
    i.status IN ('Active','Inactive','Pending') 
    AND b.status = 'ACTIVE' 
    AND i.empnumber = '1617413';

The only question is how to fill the mapping table. The most tricky part is the initially insertion.

For future emp numbers, you can use your application (clean) or a trigger (ugly) to fill the table and simply enter the logic there.

In order to fill the table correctly for the already existing emp numbers, you need to write an insert command with the exact logic you want.

I’m not entirely sure if I understand your intended logic correctly, but you should be able to fix it if I made a mistake. It should be something like this:

INSERT INTO individual_types (empnumber, individualtype)
SELECT
  i.empnumber,
  CASE WHEN
        COUNT(
          CASE WHEN b.businessunit IN ('CI-CLMS', 'CI-DIST', 'GS-CLMS', 'GS-DIST', 
                                       'PL-CLMS', 'SFCLMS', 'PI-CRC', 'PI-DRC', 
                                       'PI-FLDSLS','PI-HODST','EXTLV') THEN 1 END) > 0
        THEN 'EMP'
        WHEN COUNT(*) = COUNT(
          CASE WHEN b.businessunit IN ('DIST','CS-IA','GS-IA') THEN 1 END) 
        THEN 'IA'
        WHEN COUNT(
          CASE WHEN b.businessunit IN ('PI-IA', 'CI-Clms', 'PI-temp') THEN 1 END) > 0
          AND COUNT(
          CASE WHEN b.businessunit IN ('CI-CLMS', 'CI-DIST', 'GS-CLMS', 'GS-DIST', 
                                       'PL-CLMS', 'SFCLMS', 'PI-CRC', 'PI-DRC', 
                                       'PI-FLDSLS','PI-HODST','EXTLV') THEN 1 END) = 0
        THEN 'CWK'
        ELSE 'Other'
        END
FROM 
  individual i LEFT JOIN businessunit b
    ON i.internalid = b.internalid
GROUP BY
  i.empnumber;

If you have the optional description column, I would update it after the insertion.

See this sample fiddle that shows the idea is basically working. Adjust the insert command if necessary.

Notes: Of course, you could also produce the result without creating a table (that’s why I wrote “need”, not need).

In this case, you would use the query not to insert rows to your mapping table, but in a CTE that you join to your main query.

Or you could only create another column in your existing table rather than creating a whole table.

But I think a mapping table is clever and makes things easier.

You could, if you create a mapping table, also do this in other ways, as for example shown in this answer.

There are lot of options how to do what you want and it’s up to you to decide what’s the “best”. Depends on your use case(s).