Identify the change of record in a time table?

Asked

Viewed 372 times

0

I am working on a table that keeps the history of some people, and I need to identify and display the lines that have been modified in a certain column.

See example:

--Tabela HistSituacaoPessoa
id | nome   | situacao | data
999| Fulano | Ativo    | 2017-12-08 23:59:06.947
999| Fulano | Ativo    | 2017-12-09 23:59:06.947
999| Fulano | Inativo  | 2017-12-10 23:59:06.947
999| Fulano | Inativo  | 2017-12-11 23:59:06.947
999| Fulano | Inativo  | 2017-12-12 23:59:06.947
999| Fulano | Cobranca | 2017-12-13 23:59:06.947
999| Fulano | Ativo    | 2017-12-14 23:59:06.947
999| Fulano | Ativo    | 2017-12-14 23:59:06.947
999| Fulano | Inativo  | 2017-12-15 23:59:06.947

I need to know the line that hears the modification, in the first case shows the line that has Active, then you must show me the line that has Inactive, the next one I want to know is the line that appears the charge. With the above table example the data to be displayed should be the following:

 --Valores que devem ser exibidos baseado na tabela acima
     999| Fulano | Ativo    | 2017-12-08 23:59:06.947 
     999| Fulano | Inativo  | 2017-12-10 23:59:06.947
     999| Fulano | Cobranca | 2017-12-13 23:59:06.947
     999| Fulano | Ativo    | 2017-12-14 23:59:06.947
     999| Fulano | Inativo  | 2017-12-15 23:59:06.947

I want to know the dates that changed a person’s situation. What way can I make the query?

sql sql-server

  • I could not understand what you want! What has changed in the lines that the SELECT should return? The situation? What if you store the date of the last query somewhere and do that SELECT based on the stored date?

  • I edited it. What I want back is to know the dates a person had changed in the situation. Knowing that if Fuluna was active on the 10th and 12th he was inactive. Of course returning all the columns of the query.

  • From what I understand he wants to know what has changed in a table and who has changed and when. Essentially it would be a log? Well, in this case I imagine creating a history table, and filling it with triguers or procs as the main table changes

  • @Joetorres I consider as historical, because the table is being updated every day during the year.

  • I get it. Well you can do something like that https://docs.microsoft.com/pt-br/sql/relational-databases/track-changes/track-data-changes-sql-server where there is a replication of data. He’ll take any and all changes you make. Or if you just want to cosisa specifics, put ma Trigger in the table that saves in another table what changes have been made. or if access to this table is done by procedures only, save in these history tables the relevant changes. Anyway, it’s a reasonable but not impossible job.

  • Dude I’m not gonna put as an answer because I’m not sure it’s gonna work in your case, but try it with this SELECT:

  • SELECT id, nome, situacao, DATA FROM HistSituacaoPessoa
GROUP BY nome
ORDER BY DATA DESC

  • @Robertodecampos Thank you very much! But I found another solution.

  • @Thank you! I found the solution.

  • Why give me negative?

Show 5 more comments

1 answer

0


For this question the solution is to check the current line with the previous line, thus has the LAG function for versions from SQL Server 2012.

This being the solution:

SELECT id,
       nome,
       LAG('N',1,'S') OVER (PARTITION BY situcao ORDER BY id) as teste,
       data
FROM HistSituacaoPessoa

More information HERE and HERE

Browser other questions tagged sql sql-server

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