Select all groups that contain all specified values in a certain column

Say I have the following table:

CaseRef	NotificationReason	NotificationDate
123	SCHEDULED	2022-01-01
234	SCHEDULED	2022-01-02
312	SCHEDULED	2022-01-01
123	RESCHEDULED	2022-01-02
123	DECIDED	2022-01-03
234	DECIDED	2022-01-02

If I want to return only rows that have a CaseRef that has both a SCHEDULED and a DECIDED value in NotificationReason.

CaseRef	NotificationReason	NotificationDate
234	SCHEDULED	2022-01-02
234	DECIDED	2022-01-02
123	SCHEDULED	2022-01-01
123	RESCHEDULED	2022-01-02
123	DECIDED	2022-01-03

I wrote the below which works fine and only excludes the one row, but I was wondering if this is the most efficient way of constructing such a query?

SELECT * 
FROM @Notifications
WHERE CaseRef IN (SELECT CaseRef FROM @Notifications
                  WHERE NotificationReason = 'SCHEDULED')
  AND CaseRef IN (SELECT CaseRef FROM @Notifications
                  WHERE NotificationReason = 'DECIDED')
ORDER BY CaseRef DESC

Thanks.

Answer

Your query should be reasonably fast for checking two statuses. But in case you want to check many statuses an alternate would be:

select *
from t
where caseref in (
    select caseref
    from t
    where notificationreason in ('SCHEDULED', 'DECIDED')
    group by caseref
    having count(distinct notificationreason) = 2
)