Select data from last database file and select file data from time before last. Compare Data

Asked

Viewed 80 times

0

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

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

Inside 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 output that exists in the Now file and that did not exist in the Previous file from an hour ago

Not Found

Bring result that exists in the archiveAterior from an hour ago and that did not exist in the file Now

Modified

Bring result of both of all that has been modified

I did something kind of 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 to assemble the three consultations

sql database sql-server

  • to select the last 2 you can sort by descent and limit SELECT * FROM tb_arquivo_processado ORDER BY dtt_data_insercao DESC LIMIT 2;

  • It didn’t work for me, LIMIT

  • 1

    To query initial already does what you want. Probably your WHERE is disappearing with some records, no?

  • It’s possible, I couldn’t get it right to bring the last file and the one from the previous hour on Where

  • @13dev, 13 :) LIMIT is used for Mysql, sql server use TOP.

  • @Thais, certainly your select is doing filter by file name WHERE [vch_filename] like 'G3_OW_BUSCA_HORA_%' your data sample has no file starting with assa string.

  • @Marconciliosouza "todos estamos aqui para aprender, portanto seja amistoso e prestativo!" - ONLY ,my mistake.

  • @13dev, don’t take it the wrong way, it was just a hint.

  • @Marconciliosouza was not an error in the query, it was only at the time to give the example here, already edited

  • @Marconciliosouza of course not, thanks for the information!

  • @13dev tried for TOP, but it doesn’t answer me. I can’t get both files and compare their data, what you have in one, and what you have in the other. And anything the same, showing on a chart

  • I edited a little more, trying to clarify a few more rs

  • @Thais, there is no way to help you without knowing what you have in your table, you make some filters in your select as WHERE [vch_nome_arquivo] like 'BUSCA_HORA_%' and AND [fk_int_status] IN ('2','4') that AND has no sample of the data for we know what may or may not this bringing and the AND [dtt_data_captura] = '2017-07-12' is comparing a ??? datime field with a string ?? this can cause failures in your select.

  • Acabei de postar os campos que tenho nas tabelas: [vch_od] ,[vch_empresa] ,CONVERT(VARCHAR(10), [dtt_data], 101) AS DATE ,[vch_classe_tarifaria] ,[vch_numeros] ,CONVERT(VARCHAR(5),[dtt_hora],108) AS DTIME ,[flt_base] ,[dtt_geracao_ql2] ,[dtt_data_captura] ,[vch_nome_arquivo] ,[vch_type_file type]

  • And I’m not comparing no, I was just putting on Where the date I’m sure you’ve given, to be able to visualize if it worked

Show 10 more comments

1 answer

1


Following example below using SQL Server 2008 R2, based on microsoft’s official 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.

Browser other questions tagged sql database sql-server

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