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?
x
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.
Advertisement
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
)