SQL select statement filter

Question

Hi

I am struggling with a filter for clients in our system. Each Client has a plan that is carried out monthly. For each plan there are can be multiple visits and for each visit there can be different visit tasks with each task falling under a category e.g.

ClientNo  VisitNo  VisitTaskID   TaskCategory
------------------------------------------
900001      100        19         P
900001      100        18         P
900001      100        01         H
900001      105        21         P
900001      105        19         P
900001      105        16         C

I want to do a count for clients who receive only VisitTaskID 19 for TaskCategory 'P'. I tried using the query below but it will not filter out the other VisitTasks under category P

SELECT COUNT (ClientNo)
FROM Tasks
WHERE VisitTask NOT IN (02,03....18,20,21)

The result still counts clients with VisitTaskID's that I thought I was filtering out. Each VisitTaskID is unique no matter what category it falls under.

Any help is much appreciated.

Answer 1

Clients who only have task 19 within category p:

SELECT COUNT (ClientNo)
FROM Tasks
WHERE (VisitTask = 19 AND TaskCategory = 'P')
AND NOT EXISTS (SELECT clientno FROM tasks WHERE VisitTask =! 19 AND TaskCategory = 'P')