0
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?
Search for the DISTINCT clause: https://www.postgresql.org/docs/current/queries-select-lists.html#QUERIES-DISTINCT
– anonimo
add the distinct getting, SELECT DISTINCT (rest of query). see if it solves
– Doan Casotti
That doesn’t solve my problem, the
DISTINCT
will cause that, for example if I have 5 repeated data it will bring me only one, this is not what I need, the rule I need is informed in the post with an example to better explain.– Magno
Be clearer about which result you want.
– anonimo
@anonimo need to create this Query to run correctly on Sqlite
Nº Tel que for repetido, e tiver em ambos o STATUS = 'ANOMALIA TEMPORÁRIA' e seu MOTIVO_ENVIO for igual, importe apenas um registro, cuja DATA_ALTERACAO for mais recente.
I think it makes it easier for me to tell which filter is to be done than for my attempt to do this query and try to solve its problems, surely you must have more knowledge than me at the moment in SQL, if you can help me bro, writing this query for me, 'cause I tried hard and hard, but unfortunately!– Magno
@anonimo I edited my question, I added more examples I was the most objective only clarifying possible, I hope you understand! you find the example here too outworking
– Magno