Select data from the last file in the database and select file data from the previous time to the last one. Compare Data [closed]

0

After fetching two uploaded files, I go in the table to compare the data inside them

Between 8: 50 e 23: 50 (with interval of one in an hour) it performs a check in a file of that bank that I call Time Search with the established Standard. It looks for the latest file and the previous time file and compares.

Within this file I have the table with the fields

        dtt_data    dtt_hora    int_paradas flt_base    vch_classe_tarifaria    flt_value   vch_classe_tarifaria_1  dtt_data_captura    vch_nome_arquivo
    7/19/2017   10:55:00    1   788.9   JOOQDGZ 788.9   JOOQDGZ 7/12/2017   BUSCA_HORA_201707122000_out_ow.csv
    7/19/2017   10:55:00    1   788.9   JOOQDGZ 788.9   JOOQDGZ 7/12/2017   BUSCA_HORA_201707122000_out_ow.csv
    7/19/2017   6:05:00 1   788.9   JOOQDGZ 788.9   JOOQDGZ 7/12/2017   BUSCA_HORA_201707122000_out_ow.csv
    7/19/2017   2:00:00 1   594.9   PPOSDGZ 594.9   PPOSDGZ 7/12/2017   
BUSCA_HORA_201707122000_out_ow.csv

Then compare and bring three results:

New

Bring results that exist in the Now file and that did not exist in the Previous file an hour ago

Not Found

Bring results that exist in the file Previous 1 hour ago and that did not exist in the file Now

Modified

Bring result of the two of everything that has been modified

I did something like this:

Select * from #Agora AG
        Left Join #Agora1 AG1 ON AG.vch_classe_tarifaria_1 = AG1.vch_classe_tarifaria_1


Select * from #Agora1 AG
        Left Join #Agora AG1 ON AG.vch_classe_tarifaria_1 = AG1.vch_classe_tarifaria_1


Select * from #Agora    
except  
Select * from #Agora1

I would like help with putting together the three queries

    
asked by anonymous 07.08.2017 / 15:25

1 answer

1

Follow the example below using SQL Server 2008 R2, based on the official microsoft website:

WITH TABELA AS
(
SELECT DISTINCT HireDate 
FROM [AdventureWorks2008R2].[HumanResources].[Employee]
)
SELECT TOP 2 ROW_NUMBER() OVER(ORDER BY HireDate DESC) AS Row, HireDate
FROM TABELA
ORDER BY HireDate DESC

Reference.

    
07.08.2017 / 21:40