VBA - SQL - Filter with SELECT

Question

VBA - SQL - Filter with SELECT

Navigation

#1 by (3 votes)

8

I would like help with extracting information from another worksheet through SQL.

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

The link to the spreadsheet where the Database is containing the information for the extraction is this: By Google Drive

I have a worksheet where you have the fields:    Tel No., Reason for Submission, Activation Date, Date Import Date Change, Status.

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

Tel number that does not repeat and has Status (RELEASED) does not need to be imported.
Tel number that does not repeat and has Status (TEMPORARY ANOMALY, PENDING, ANALYSIS) needs to be imported.
li>
You do not need to import the Status (DEFINITIVE ANOMALY)
Tel number that is repeated and has Status (TEMPORARY ANOMALY, PENDING, ANALYSIS) needs to be imported.
DO NOT IMPORT Tel (phone numbers) that repeat RELEASED , that is, if you have 4 numbers repeated for example, and one of them appears the RELEASED Status none of these numbers will be imported.

I've tried in many ways, but to no avail, 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

asked by anonymous 17.11.2018 / 23:00

1 answer

How to implement the "evaluate ()" and "successor ()" method of the Hill Rise algorithm? What is the best type in SQL to use with CryptoCurrency?

score 3 · Accepted Answer

You will have to use a subquery, which is nothing more than SELECT inside another SELECT . The query looks 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 is any number with Status (RELEASED) and NOT EXISTS excludes it:

    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 looks like this: