Browse previous record - SQL Server 2012

Question

I need to bring only the records where the previous status was 1, but which is currently 0.

I have a table called solicitação containing a field called VencimentoPausado, where this field values are 1 or 0, that is to say, 1 Chamados que estão em pausa and 0 Chamados que não estão em pausa. I want to bring all the calls that are no longer on pause, ie before the era drive 1 and after the drive is 0.

Remembering that if I put in the condition vencimentpausado = 0, it will bring all called, but I want to bring only those called that before the status was 1.

Table Request

**Coluna**  **Tipo**
AmbienteID     int
AprovacaoRevisao    bit
CalcularVencimentoAprovacao bit
ChamadoIntegradoID  int
ConcluidoNoAtendimento  bit
ConcluidoPeloSistema    nchar
CustoTotal  numeric
DataAberturaSistema datetime
DataAlteracao   datetime
DataAprovacao   datetime
DataBaseReabertura  datetime
DataBaseSlaDinamico datetime
DataCancelamento    datetime
DataCompromisso datetime
DataEntendimento    datetime
DataPrevistaPriResp datetime
DataPrevistaRetorno datetime
DataRealFechamento  datetime
DataRealPriResp datetime
DataRealRetorno datetime
Descricao   varbinary
EPGerada    bit
FusoHorarioID   int
HorarioVeraoHistoricoID int
ImportadoAmbienteIntegrado  bit
Incidente   bit
IntegracaoConcluida bit
IntegracaoPausada   bit
MacProcID   int
MailPriRespEnviado  nchar
ModID   int
MotivoCompromissoID int
NatID   smallint
Natureza    nvarchar
NomeCliente nvarchar
Organiza    nvarchar
OrgID   int
OrgIDGrp    int
PermitePausarVencimento bit
PermiteReclassificarVencimento  bit
Produto nvarchar
ProID   int
Projeto nvarchar
ProjID  int
RecalculaVencimentoDataVisita   bit
ReplicadoAmbienteIntegrado  bit
Sequencia   numeric
SeveridadeID    int
SLAVlCalculado  int
SLAVlRevCalculado   int
SolAlterado nchar
solAprovado nchar
SolArqAtachado  nchar
SolArqNome  nvarchar
SolAssumido smallint
SolCalcularVencimento   nchar
SolCaminho  nvarchar
SolData datetime
SolDataConclusao    smalldatetime
SolDataFechamento   datetime
SolDataVisita   smalldatetime
SolEnviaEmailTramite    nchar
SolEnvioEmail   datetime
SolEstagioID    int
SolGrupoSAC nchar
SolicitacaoPai  int
SolID   int
SolIDCopia  int
SolIDInclusao   int
SolIDRef    nvarchar
SolNivel    int
SolNumTramite   int
SolOrigem   nchar
SolPerAlcada    int
SolPrioridade   smallint
SolQtdeReaberturas  smallint
SolQtdFilho int
SolQtdTarFilho  int
SolRetrabalho   nchar
SolRetrabalhoResp   nchar
SolStatus   tinyint
SolSugestao nchar
SolTempoTelefone    int
SolTipID    int
SolTipoVinculo  smallint
SolTitulo   nvarchar
SolVencimento   datetime
SolVerL int
SolVerO int
TempoCorridoSLADinamico bit
TempoGastoTotal int
TempoPriResp    int
TempoRestanteVencimento int
TempoSlaReabertura  int
TipoDescricao   nvarchar
TipoFechamentoAutomatico    int
TituloChamadoIntegrado  nvarchar
UsuCentrodeCusto    nvarchar
UsuIDAssociado  int
UsuIDAtendenteViaFone   int
UsuIDCliente    int
UsuIDDistribuidor   int
usuIDFilial int
UsuIDGrupoRespConclusao int
UsuIDInclusao   int
UsuIDMatriz int
UsuIDReclamante int
UsuIDReclamanteAssociado    int
UsuIDReclamanteDistribuidor int
usuIDReclamanteRepresentado int
usuIDRepresentado   int
UsuIDResponsavel    int
UsuIDSuporte    int
UsuIDUltimaAlteracao    int
UsuIDUltimoResp int
UtilizaSLADinamico  bit
ValorServico    numeric
ValorServicoCaminho numeric
ValorTotal  numeric
VencimentoChamadoIntegrado  bit
VencimentoInformadoManualmente  bit
VencimentoPausado   bit
VersaoL nvarchar
VersaoO nvarchar

Answer 1

Editing

Looking at that question better today, it doesn’t seem very clear. I just wanted to add that to query previous/previous values, in another line, you can use window functions (windowing).

Example

Simple DB Fiddle

-- Exemplo de multiplicação usando o valor atual e o valor anterior de 'coluna'
LAG(coluna) OVER (PARTITION BY Id ORDER BY Id ASC) * coluna AS multiplicacao

Original

In the SQL Server you could use the magic tables when using triggers.

However, normally you would need tables of log for the normal operation of your system, in case you need to redeem previous values.

If this is not the case, you need to check the log of transactions/transaction log. See how!

See also about change data capture.

Answer 2

You can use a WITH to sort by request and link to the previous and the next.

WITH timeline (SolID,
               ChamadoIntegradoID,
               Ordem,
               VencimentoPausado)
AS (
  SELECT s.SolID,
         s.ChamadoIntegradoID,
         ROW_NUMBER() OVER(PARTITION BY s.ChamadoIntegradoID
                           ORDER BY s.DataAberturaSistema) AS Ordem,
         VencimentoPausado
    FROM solicitacao s
)

SELECT atual.SolID,
       atual.ChamadoIntegradoID,
       atual.VencimentoPausado
  FROM timeline atual
       INNER JOIN timeline anterior ON anterior.ChamadoIntegradoID = atual.ChamadoIntegradoID
                                   AND anterior.Ordem = (atual.Ordem - 1)
 WHERE atual.VencimentoPausado = 0
   AND anterior.VencimentoPausado = 1
   AND NOT EXISTS(SELECT 1
                    FROM timeline proximo
                   WHERE proximo.ChamadoIntegradoID = atual.ChamadoIntegradoID
                     AND proximo.Ordem > atual.Ordem;

---

In the query above:

• The WITH is used to group the content that will be used to obtain and filter the data;
• The ROW_NUMBER with PARTITION BY for codigo_chamado shall assign a sequential within each grouping and the ORDER BY ensure that this sequence follows the insertion order;
• Using the resulting data, a INNER JOIN is performed with table data by merging the current and previous records;
• The restriction of the column VencimentoPausadois made in the WHERE;
• A clause NOT EXISTS is used to verify that the current record is the last valid.

---

WITH

Specifies a temporary named result set, known as a CTE (common table expression). It is derived from a simple query and defined in the execution scope of a single SELECT, INSERT, UPDATE, MERGE or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of the SELECT statement that defines it. A common table expression can include references to itself. It is what we call the common recursive table expression.

---

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

---

EXISTS

Specifies a sub-query to be tested for lines.