How do I not select repeated data that contains a specific value in SQL in a column?

Question

Good evening guys, I’m beginner with Database, I have a Query that runs a filter following a rule:

Rule:

GSM which is repeated, and has in both STATUS = 'TEMPORARY ANOMALY' and its MOTIVO_ENVIO is the same, only import one record, whose DATA_ALTERACAO is more recent.

I made the filter as follows:

select ex.[Nº Tel], ex.[Nº Contrato], ex.[Motivo de Envio], ex.[Status], ex.[Data Ativação], ex.[Data Importação], ex.[Data Alteração], ex.[CPF/CNPJ], ex.[IMEI], ex.[Usuário], ex.[Filiais], ex.[Descrição Anomalia]
from Sheet1 ex
where ex.[Status] = 'ANOMALIA TEMPORÁRIA'
and exists (
            select tmp.[Nº Tel]
            from Sheet1 tmp
            where tmp.[Nº Tel] = ex.[Nº Tel]
            and tmp.[Motivo de Envio] = ex.[Motivo de Envio]
            and tmp.[Status] = ex.[Status]
            group by tmp.[Nº Tel], tmp.[Motivo de Envio], tmp.[Status]
            having count(tmp.[Nº Tel]) > 1)

I will give an example of how it is to be done:

You can see that the camp GSM is repeating, The field MOTIVO_ENVIO is also repeating, the field STATUS is with 'TEMPORARY ANOMALY' and if repeating, that is what I need, only that out of all these records I will take what is with the DATA_ALTERACAO more recent, because that’s what I need.

But I’m having a problem, the result brings me repeated data in which some records are with the Status that contains the values ('LIBERADO', 'PENDENTE', 'EM ANÁLISE', 'ANOMALIA DEFINITIVA') and that I can’t have.

But it’s not just giving one WHERE Status NOT IN ('LIBERADO', 'PENDENTE', 'EM ANÁLISE', 'ANOMALIA DEFINITIVA') because it would only remove the record that contains this Status, I need you to remove all the Nº Tel have these Status.

Example:

     Nº Tel               Motivo de Envio                Status
_________________________________________________________________________
| 31994202248 | PRIMEIRA ATIVAÇÃO_REEMBOLSO - ADM | LIBERADO            |
| 31994202248 | PRIMEIRA ATIVAÇÃO_REEMBOLSO - ADM | ANOMALIA TEMPORÁRIA |
| 31994202248 | PRIMEIRA ATIVAÇÃO_REEMBOLSO - ADM | ANOMALIA TEMPORÁRIA |

As seen above the Nº Tel is repeating, the Motivo de Envio is equal, the Status is with 'TEMPORARY ANOMALY' only that you’re also with LIBERATED that is, I do not need these records, in case they cannot appear in the final result. I gave an example with the Status = 'LIBERADO' but the same applies to Status = PENDENTE, EM ANÁLISE, ANOMALIA DEFINITIVA

Someone?

Answer 1

select 
    ex.[Nº Tel],
    ex.[Nº Contrato],
    ex.[Motivo de Envio],
    ex.[Status],
    ex.[Data Ativação],
    ex.[Data Importação],
    ex.[Data Alteração],
    ex.[CPF/CNPJ],
    ex.[IMEI],
    ex.[Usuário],
    ex.[Filiais],
    ex.[Descrição Anomalia]
from Sheet1 ex
-- Inner que limita os resultados
inner join (
    select
        ex2.[Nº Tel], ex2.[Nº Contrato], max(ex2.[Data Alteração]) data_alteracao
    from Sheet1 ex2
    group by x2.[Nº Tel], ex2.[Nº Contrato]
) limite on limite.[Nº Tel] = ex.[Nº Tel] and limite.[Nº Contrato] = ex.[Nº Contrato] and limite.data_alteracao = ex.[Data Alteração]
-- FIM: Inner que limita os resultados
where ex.[Status] = 'ANOMALIA TEMPORÁRIA'
and exists (
    select tmp.[Nº Tel]
    from Sheet1 tmp
    where tmp.[Nº Tel] = ex.[Nº Tel]
        and tmp.[Motivo de Envio] = ex.[Motivo de Envio]
        and tmp.[Status] = ex.[Status]
        group by tmp.[Nº Tel], tmp.[Motivo de Envio], tmp.[Status]
        having count(tmp.[Nº Tel]) > 1
    )