How to make a codition inside a NOT IN on Sqlserver

Question

Suppose I have the following data, license plates, category, and situation:

1- 1 - Encerrado
2- 10 - Concluído
3- 11 - Pendente
5 - 2 - Encerrado
6 - 1 - Pendente
9- 10- Encerrado

And I want to do a select that presents me the results by removing the plates 5,6,7. But, if the record is equal to Closed, of these enrollments 5,6,7, must be presented.

That is, the final result should present the matricules 1,2,3,5 and 9.

How to do?

I tried it this way:

WHERE t.categoriaId IN (1,2,10,11)
AND (t. matricula NOT IN ( 5,6,9 ) AND situacao 'Encerrado')

But it removes me all the records that are different from closed, not just within the set 5,6,9.

How to do?

Answer 1

By your explanation I deduce you want to remove the plates 5,6,7 this is:

t. matricula NOT IN (5, 6, 7)

but if the situation is 'Closed' then consider, regardless of registration, then use:

t. matricula NOT IN (5, 6, 7) OR situacao = 'Encerrado'

Answer 2

Hello, all right?

You must replace the first AND by the OR as mentioned below:

     WHERE t.categoriaId IN (1,2,10,11)
     OR (t. matricula NOT IN ( 5,6,9 ) AND situacao 'Encerrado')

Hugs!