How to check different data and get the first SQL record?

Asked

Viewed 136 times

0

I need to create a filter in SQL that follows some rules, I have a spreadsheet in Excel and in it I need to apply the following filter, the spreadsheet has the fields:

  • GSM
  • MOTIVO_ENVIO
  • DATA_ATIVACAO
  • DATA_IMPORTACAO
  • DATA_ALTERACAO
  • STATUS

Filter:

GSM which repeats itself and has the STATUS = "PENDING", "UNDER REVIEW" but its MOTIVO_ENVIO is different, only import the line that has the STATUS equal to "TEMPORARY MALFUNCTION"

I tried in many ways, but unsuccessfully, I don’t have as much knowledge with Database and so I would like your help, as far as I got was:

SELECT [GSM],
       [MOTIVO_ENVIO],
       [DATA_ATIVACAO],
       [DATA_IMPORTACAO],
       [DATA_ALTERACAO],
       [STATUS]
FROM [export$] AS export
WHERE EXISTS
    (SELECT aux.[GSM]
     FROM [export$] AS aux
     WHERE export.[GSM] = aux.[GSM]
       AND [STATUS] = "PENDENTE")
  AND [STATUS] = "ANOMALIA TEMPORÁRIA"

But I couldn’t place one of the most important parts of the filter:

...your MOTIVO_ENVIO is different, only import the line that has the STATUS equal to "TEMPORARY MALFUNCTION"


The below follows an example of how would be the structure that is found in the spreadsheet, just apply the filter now so that I can extract the "TEMPORARY ANOMALY" according to the rules mentioned.

Filtro realizado para exemplo.

I would love to help you, I thank you in advance!

sql sql-server excel

  • Share table with some data, can be in Pastebin in sharecsv ...

  • @thiaguerd of course, here it is complete: https://drive.google.com/file/d/1wXHhvI9In4j_lrwB3dzcyHsm5jThlkcQ/view

  • I changed the column names, the changes were: Nº Tel = GSM | Sending Reason = MOTIVO_ENVIO | Activation Date = DATA_ATIVACAO | Import Date = DATA_IMPORTACAO | Change Date = DATA_ALTERACAO | Status = STATUS | I will edit the post to leave everything straight :)

  • Paste this spreadsheet into table format for me to play your scenario and try to help.

  • @Ismael https://drive.google.com/open?id=1S1KKp6qKw1nykDK4nZRWO0O4CzZRxAS0

  • I don’t have access :( the company restricts, so I have some limitations... The question would need to be edited.

Show 1 more comment

1 answer

1

The difficulty continues in understanding what you need, but come on, in parts.

I understood that you need: GSM that repeats and has the STATUS = "PENDING", "UNDER ANALYSIS"

You find that answer with:

select
    count(gsm) as "repeticoes_do_gsm",
    gsm
from
    expt
where
    status in  ('PENDENTE', 'EM ANÁLISE')
GROUP BY
    gsm
HAVING
    count(gsm) > 1

Anyway, you said, "but your MOTIVO_ENVIO is different, "

Then I ask you, MOTIVO_ENVIO is different than what?

List there what you need most, step by step, without fear.

Browser other questions tagged sql sql-server excel

You are not signed in. Login or sign up in order to post.