Paste previous record - SQL Server 2012

Question

Paste previous record - SQL Server 2012

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

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

I have a table named solicitação that contains a field called VencimentoPausado , where this field values are 1 or 0 , that is, 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 paused, that is, before the move was 1 and after the move it is 0 .

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

Request Table

**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

sql sql-server query

asked by anonymous 06.08.2017 / 19:57

2 answers

2

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

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

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

See also about change data capture .

06.08.2017 / 20:42

Import multiple libs into a single include in c Change existing column to Identity

score 0 · Accepted Answer

You can use WITH to sort by request and link to previous and 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% with% above:

The query is used to group the content that will be used in obtaining and filtering the data;
% with% with% with% with% will assign a sequence within each group and% with% will ensure that the sequence will follow the order of insertion;
Using the resulting data, a WITH is performed with the table data by joining the current and previous record;
Constraint of column ROW_NUMBER is done in PARTITION BY ;
A codigo_chamado clause is used to check if the current record is the last valid.

ORDER BY

Specifies a temporary named result set, known as a common table expression (CTE). It is derived from a simple, defined query 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 may include references to itself. This is what we call recursive common table expression.

INNER JOIN

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

VencimentoPausado

Specifies a subquery to be tested for rows.