Identify the change of record in a temporal table?

Navigation
0

I'm working on a table that keeps track of some people, and I need to identify and display rows that have undergone some modification in a particular 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 it shows the line that has Active, then it should show me the line that has Inactive, the next one I want to know is the line that appears the collection. With the example in the table above 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 which dates have changed in a person's situation. How can I make the query?

    
asked by anonymous 19.12.2017 / 18:13

1 answer

0

For this question the solution is to check the current row with the previous row, so you have the LAG function for the versions from SQL Server 2012.

So 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

    
19.12.2017 / 19:44
Chart.js installation failed How to use DrawToBitmap in WPF to take print from screen?