VBA - SQL - Perform filter with SELECT

Asked

Viewed 877 times

9

I would like a help in extracting information from another spreadsheet through SQL.

The spreadsheet link where you will establish the connection and extract the information is this: By Google Drive

The spreadsheet link where is the Database, where contains the information for the extract is this: By Google Drive


I have a spreadsheet where you have the fields: Nº Tel, Reason for Sending, Activation Date, Import Date Change Date, Status.

I need to import all of them, with some criteria (except):


  • Nº Tel that does not happen again and is with the Status (RELEASED) does not need to be imported.

  • Nº Tel that does not happen again and is with the Status (TIME ANOMALY, PENDING, UNDER REVIEW) NEEDS TO BE IMPORTED.

  • Status does not need to be imported (DEFINITIVE ANOMALY)

  • Nº Tel which is repeated and has no Status (TIME ANOMALY, PENDING, UNDER REVIEW) NEEDS TO BE IMPORTED.

  • NEVER MIND the Nº Tel (phone numbers) that repeat IF has in any of them the Status LIBERATED, that is if you have 4 numbers repeated for example, and in one of them appears the RELEASED Status none of these numbers will be imported.


I tried in many ways but unsuccessfully, I’m still a beginner with SQL, I made the code below but it does not follow the rules mentioned above.

StrSql = "SELECT [Nº Tel], [Motivo de Envio], [Data Ativação], [Data Importação], [Data Alteração], Status FROM [export$] WHERE Status in ('ANOMALIA TEMPORÁRIA','PENDENTE','EM ANÁLISE')"
sql excel select vba

1 answer

3


You will have to use a subquery, which is nothing more than a subquery SELECT within another SELECT. The query was like this: 

SELECT [Nº Tel], [Motivo de Envio], [Data Ativação], [Data Importação], [Data Alteração], [Status]
FROM `C:\xxx\APURAR.xlsx`.`export$` AS export
WHERE
NOT EXISTS (
    SELECT DISTINCT aux.[Nº Tel]
    FROM `C:\xxx\APURAR.xlsx`.`export$` AS aux
    WHERE export.[Nº Tel] = aux.[Nº Tel] 
        AND aux.[Status] = 'LIBERADO'
)
AND [Status] IN ('ANOMALIA TEMPORÁRIA','PENDENTE','EM ANÁLISE')
ORDER BY [Nº Tel]

The subquery below checks if there are any numbers with the Status (RELEASED) and the NOT EXISTS excludes him:

    SELECT DISTINCT aux.[Nº Tel]
    FROM `C:\xxx\APURAR.xlsx`.`export$` AS aux
    WHERE export.[Nº Tel] = aux.[Nº Tel] 
        AND aux.[Status] = 'LIBERADO'

The result was so:

Resultado

  • Excellent my friend worked perfectly, only that I am beginner with Database, you could explain me every line you wrote and describe what each thing does, because I do not like to only copy and paste, but rather understand what was done :D

  • 1

    So I recommend you get a T-SQL book. Or take this course free in the mva

  • 1

    But the explanation is how it is in the same answer. Only the phones will be included when the subquery does not find anything, so the NOT EXISTS. And I make the logical subquery merge with export.[Nº Tel] = aux.[Nº Tel]. Since the two are in the same table, I call the table in the subquery of aux.

  • Friend, you had helped me last time, only now it’s a new process to realize the filters, could you help me? the new rules are those: rules here the database remains the same: data here If you can help me thank you!

Browser other questions tagged sql excel select vba

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