CASE WHEN PARTITION BY - Returning Value for Groups with Two Specific Values in Same Column Across Group

As the title says, I'm having trouble returning groups that have two specific values across the group in the same column.

Here is an example of the data:

Claim | Exposure | Product
1       1          B
1       2          C
2       1          B
2       2          D

My goal is essentially create a third column that specifies if a Claim has a Product = B for any of it's exposures and a Product = C for any of it's exposures (i.e. "All required present"). If the claim is missing an exposure with a specific product, I want to return something like "Product C missing."

Ideally it would result in the following:

Claim | Exposure | Product | RequiredCheck
1       1          B         All Required
1       2          C         All Required
2       1          B         Missing C
2       2          D         Missing C

I've tried to use a GROUP BY Claim HAVING Product = B and Product = C, but this isn't working because the check is based on the same column across a group of claims, and I don't necessarily want to limit my results to only those with "All Required" values.

I've also try to use a MAX(CASE WHEN Product = B and Product = C THEN 1 ELSE 0 END) PARTITION BY Claim) but that also wouldn't return any results because the check is based on a value in the same column.

I have thought about coding the products to be a specific number (i.e A = 1, B = 2) and then using SUM OVER PARTITION BY, but this also won't work because I could have a claim with multiple exposures with the same product and I can't just say where the count > 5 because it could have 5 exposures with the product A.

Any help is appreciated!

Solution

Use case with window functions:

select t.*,
       (case when num_b > 0 and num_c > 0 then 'All required'
             when num_b > 0 then 'C missing'
             when num_c > 0 then 'B missing'
             else 'Both missing'
        end) as requiredcheck
from (select t.*,
             sum(case when product = 'B' then 1 else 0 end) over (partition by claim) as num_b,
             sum(case when product = 'C' then 1 else 0 end) over (partition by claim) as num_c
      from t
     ) t;