Is there any way to have a solution for this SQL statement?

Question

enter image description here

Query I used here is to get claimstatus by using case statement. But I am not getting the expected output.

For Num : 040523000374 (1st and 2nd row), where EventName does not have 'ClaimReassigned', then claimStatus is 'Not ClaimReassigned', it's working fine

But for Num : 047523000125 (3rd to 6th row) where EventName is ClaimReassigned for one of the Num, then ClaimStatus should be 'ClaimReassigned' for that number (047523000125) from 3rd to 6th row.

I used CASE statement as shown here:

CASE EventName
    WHEN 'ClaimReassigned' THEN 'ClaimReassigned'
    ELSE 'Not ClaimReassigned'
END

Answer 1

use the window function with Partition by Num and count() any rows with EventName equal to ClaimReassigned

claimStatus = case when count(case when EventName = 'ClaimReassigned' 
                                   then 1 end) over (partition by Num) >= 1
                   then 'ClaimReassigned'
                   else 'Not ClaimReassigned'
                   end