Recriar Histórico com base em uma DataInicio/DataFim

[Marco André]

Olá Pessoal,

Estou tentando alterar uma tabela de Histórico, onde dada uma nova entrada, a tabela seja recriada (apenas para os registros de mesmo ID). As colunas DataAtivacao e DataDesativacao de todos os registros de mesmo ID deverão sofrer uma "reordenação" em suas datas. Por exemplo: o registro anterior deverá ter a DataDesativacao alterada para "DataAtivacao do registro atual - 1".

Segue o esquema do banco de dados. Em seguida um exemplo de input/output do resultado esperado e também do que já tentei fazer para a solução do problema.

Tabela de Histórico:

CREATE TABLE [dbo].[HistoricoInventario] (
  [HistoricoID] bigint IDENTITY(1, 1) NOT NULL,
  [DispositivoID] int NOT NULL,
  [DataAtivacao] datetime NOT NULL,
  [DataDesativacao] datetime CONSTRAINT [DF__Historico__DataD__66603565] DEFAULT '3000/01/01' NULL,
  CONSTRAINT [HistoricoInventario_pk] PRIMARY KEY CLUSTERED ([HistoricoID], [DispositivoID], [DataAtivacao]),
  CONSTRAINT [HistoricoInventario_uq] UNIQUE ([DispositivoID], [DataAtivacao]),
)
ON [PRIMARY]
GO

O problema é que tenho que manter atualizado o histórico, ou seja, as datas de ativação e desativação. Até aqui, não há nenhuma problema, a não ser que tenho que manter as datas sempre “fazendo sentido”, digo, quando eu alterar a data de ativação/desativação de um registro, tenho que recriar todos os outros históricos contendo o mesmo DispositivoID em questão, ex.: Tenho o seguinte Histórico, cujo DispositivoID=1:

ID    DispositivoID   DataAtivacao    DataDesativacao
1    1        01/01/2010    05/01/2010    
2    1        06/01/2010    20/01/2010    
3    1        21/01/2010    22/01/2010

Se a DataAtivacao do registro com ID=2, for alterada para o dia 08/01/2010 e a DataDesativacao para 22/01/2010 o resultado desta alteração deveria ser o seguinte:

ID     DispositivoID    DataAtivacao    DataDesativacao
1    1        01/01/2010    07/01/2010    
2    1        08/01/2010    22/01/2010    
3    1        23/01/2010    23/01/2010

O máximo que consegui fazer, foi alterar a DataAtivacao/DataDesativacao do registro anterior(apenas 1 registro anterior), criando a seguinte trigger na tabela de Historico:

CREATE PROCEDURE dbo.GetLastDataDesativacao 
  @DispositivoID int, 
  @HistoricoIDAnterior bigint OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE @HistoricoIDAtual BIGINT
  
  DECLARE @DataNew datetime, @DataOld datetime
  
  SELECT @HistoricoIDAtual = MAX(HI.HistoricoID)
  FROM Funcoes.dbo.HistoricoInventario HI
  WHERE HI.DispositivoID=@DispositivoID
  
  SELECT TOP 1 
    @HistoricoIDAnterior = HistoricoID, 
    @DataOld = b.DataDesativacao,
    @DataNew = (SELECT TOP 1 DataDesativacao FROM Funcoes.dbo.HistoricoInventario a WITH(NOLOCK)
                WHERE a.DispositivoID = b.DispositivoID AND
                a.DataDesativacao < b.DataDesativacao
                )
  FROM Funcoes.dbo.HistoricoInventario b WITH(NOLOCK)
  WHERE b.DispositivoID = @DispositivoID AND 
    b.HistoricoID <= 
      (SELECT MAX(HistoricoID) FROM Funcoes.dbo.HistoricoInventario 
       WHERE DispositivoID = @DispositivoID) 
  ORDER BY b.DataDesativacao DESC  
  
  RETURN @HistoricoIDAnterior
END

CREATE TRIGGER [dbo].[HistoricoInventario_tri] ON [dbo].[HistoricoInventario]
WITH EXECUTE AS CALLER
INSTEAD OF INSERT
AS
BEGIN
-- Ao invés de inserir, faça o seguinte:
  DECLARE 
    @id int, 
    @DataAtivacao datetime, 
    @DataDesativacao datetime,
    @UltimaDesativacao datetime,
    @HistoricoIDAtual bigint,
    @HistoricoIDAnterior bigint,
    @DebugData nvarchar(200)

  SELECT 
    @id              = I.DispositivoID,
    @DataAtivacao    = I.DataAtivacao,
    @DataDesativacao = I.DataDesativacao
  FROM INSERTED I
  
  --Pega o código do historico
  --Se já existe um historico com o mesmo dispositivo, 
  --então altera a data de desativacao do ultimo registro 
  --para 1 dia antes da data de ativacao do registro sendo inserido
  IF EXISTS (SELECT HC.DispositivoID FROM Funcoes.dbo.HistoricoInventario HC
             WHERE HC.DispositivoID = @id)
  BEGIN
    -- Atualiza o registro anterior
    -- Altera a data de desativacao do registro anterior
    EXEC Funcoes.dbo.GetLastDataDesativacao @id, @HistoricoIDAnterior OUTPUT
   
    UPDATE Funcoes.dbo.HistoricoInventario SET DataDesativacao = DATEADD(day,-1,@DataAtivacao)
    WHERE DispositivoID = @id 
    AND HistoricoID = @HistoricoIDAnterior
    
    -- Insere o novo registro
    IF @DataDesativacao IS NOT NULL 
      INSERT INTO Funcoes.dbo.HistoricoInventario(DispositivoID,DataAtivacao,DataDesativacao)
      VALUES (@id,@DataAtivacao,@DataDesativacao)
    ELSE
      INSERT INTO Funcoes.dbo.HistoricoInventario(DispositivoID,DataAtivacao)
      VALUES (@id,@DataAtivacao)
  END  
  ELSE
    IF @DataDesativacao IS NOT NULL 
      INSERT INTO Funcoes.dbo.HistoricoInventario(DispositivoID,DataAtivacao,DataDesativacao)
      VALUES (@id,@DataAtivacao,@DataDesativacao )
    ELSE
      INSERT INTO Funcoes.dbo.HistoricoInventario(DispositivoID,DataAtivacao)
      VALUES (@id,@DataAtivacao)
  
  RETURN
END
GO

Este procedimento funciona apenas para o primeiro registro anterior, o que não resolve o problema. A tabela deve ser recriada de acordo com o input.

Espero ter sido feliz na exposição do problema.

Qualquer ajuda será de grande valia.

Muito obrigado,

Marco André

[fulvio]

Bom dia Marco,

Creio que tenha entendido seu problema... rs.

Olhei apenas o que estava precisando. O seu script de alteração apenas corri o olho. Tentei fazer um aqui pra resolver o problema.

Criei uma temporária baseada no exemplo de tabela que postou. Aí fiz o script para manipulação dos dados.

Postei logo abaixo o script. Dá uma olhada se é isso que deseja.

Qualquer dúvida, pode postar.

CREATE TABLE #HistoricoInventario (
  [HistoricoID] bigint,
  [DispositivoID] int NOT NULL,
  [DataAtivacao] datetime NOT NULL,
  [DataDesativacao] datetime CONSTRAINT [DF__Historico__DataD__66603565] DEFAULT '3000/01/01' NULL
)
ON [PRIMARY]
GO

-- Inserção dos dados
insert into #HistoricoInventario ([HistoricoID], [DispositivoID], [DataAtivacao], [DataDesativacao]) values 
(1,1,'2010-01-01', '2010-01-05')
insert into #HistoricoInventario ([HistoricoID], [DispositivoID], [DataAtivacao], [DataDesativacao]) values 
(2,1,'2010-01-06', '2010-01-20')
insert into #HistoricoInventario ([HistoricoID], [DispositivoID], [DataAtivacao], [DataDesativacao]) values 
(3,1,'2010-01-21', '2010-01-22')

-- Conferência
select * from #HistoricoInventario

-- Rodar tudo, até o final
Declare @Id int
Declare @DataAtivacao datetime
Declare @DataDesativacao datetime
Declare @Contador int

Set @Id = 2
Set @DataAtivacao = '2010-01-08'
Set @DataDesativacao = '2010-01-22'

update #HistoricoInventario set DataAtivacao=@DataAtivacao, DataDesativacao=@DataDesativacao where HistoricoID = @Id

update #HistoricoInventario set DataAtivacao = (dateadd(d, 1, @DataDesativacao)), 
DataDesativacao = (dateadd(d, 1, @DataDesativacao))
where DataAtivacao between @DataAtivacao and @DataDesativacao and HistoricoID <> @Id

update #HistoricoInventario  set DataAtivacao = (dateadd(d, 1, @DataDesativacao)), 
DataDesativacao = (dateadd(d, 1, @DataDesativacao))
where @DataDesativacao between @DataDesativacao and @DataAtivacao and HistoricoID <> @Id

------------------------------------------------

-- Conferência
select * from #HistoricoInventario

[Marco André]

Olá Fulvio, bom dia.

Obrigado pela resposta!

Vou testar sua solução. Para ser realmente bem simples e funcional. Mas só um comentário: a variável @Contator parece não estar sendo utilizada. Era para ter um loop ali?

Vou deixar também o link de um outro post que crie com outra possível solução. Segue:

http://www.experts-exchange.com/Microsoft/...Q_26310725.html

Mais uma vez, obrigado!

Att,

Marco André

Editado Julho 10, 2010 por Marco André

[fulvio]

Bom dia Marco,

A variável @Contator criei para fazer uns testes. Realmente ela não é necessária (esqueci de retirar... rs).

Outra coisa: são três updates. O ultimo update coloquei para certificar que todos os dados serão consistidos. Se sua base estiver ok, este update não será necessário.

Vou dar uma olhada no outro post também. Faça os testes e depois me fala!!! :.)