Ir para conteúdo
Fórum Script Brasil

Marco André

Membros
 Perfil  Explorar Conteúdo

  • Total de itens

    3

  • Registro em

  • Última visita

 Tipo de Conteúdo 

Posts postados por Marco André

  1. Recriar Histórico com base em uma DataInicio/DataFim

    em SQL Server

    Postado · Editado por 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é

  2. Recriar Histórico com base em uma DataInicio/DataFim

    em SQL Server

    Postado

    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é

  3. Como retornar data e duração onde data e suração são iguais, porém os

    em SQL Server

    Postado · Editado por kuroi
    Adicionar tag CODE

    Olá pessoal,

    Tenho 2 tabelas onde preciso listar apenas registros que sigam os seguintes requisitos:

    1-Registros tem que ter a mesma data, duração e número discado (Calls.Start, Calls.Duration, Calls.PhoneNo)

    2-Registros tem que ter valores diferentes (CallsInfo.Cost)

    É possível este tipo de consulta? Já tentei fazer isso de várias formas, mas não estou conseguindo.

    Segue as tabelas:

    CREATE TABLE [dbo].[Calls] (
  [CallID] bigint IDENTITY(1, 1) NOT NULL,
  [Start] datetime NOT NULL,
  [Duration] int NOT NULL,
  [DeviceID] int NOT NULL,
  [ReferenceID] int NOT NULL,
  [DataSourceID] int NOT NULL,
  [CallTypeID] smallint NOT NULL,
  [PhoneNo] nvarchar(30) COLLATE Latin1_General_CI_AS NULL,
  [OriginalPhoneNo] nvarchar(30) COLLATE Latin1_General_CI_AS NULL,
  [StartTimeInSeconds] int NULL,
  [Ext_DeviceID] int NULL,
  [TransferExt_DeviceID] int NULL,
  [TransferInExt_DeviceID] int NULL,
  [DestExt_DeviceID] int NULL,
  [TrunkID] int NULL,
  [Trunk2ID] int NULL,
  [ContextID] int NULL,
  [ReferenceID2] int NULL,
  [TLRStatus] smallint NULL,
  [TLRDeleted] bit NULL,
  [Deleted] tinyint CONSTRAINT [DF_Calls_Deleted] DEFAULT 0 NULL,
  [Arrived] datetime NULL,
  [MaskDuration] nvarchar(30) COLLATE Latin1_General_CI_AS NULL,
  [MaskPhoneNo] nvarchar(30) COLLATE Latin1_General_CI_AS NULL,
  [SessionID] int NULL,
  CONSTRAINT [Calls_PK] PRIMARY KEY CLUSTERED ([CallID]),
  CONSTRAINT [FK_Calls_CallTypes] FOREIGN KEY ([CallTypeID]) 
  REFERENCES [dbo].[CallTypes] ([CallTypeID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
)
ON [PRIMARY]
GO
    CREATE TABLE [dbo].[CallsInfo] (
  [CallID] bigint NOT NULL,
  [BP] char(1) COLLATE Latin1_General_CI_AS NULL,
  [CallCategoryID] int NULL,
  [LocationID] int NULL,
  [CityLocationID] int NULL,
  [ServiceID] int NULL,
  [Cost] money NULL,
  [OriginalCost] money NULL,
  [Price] money NULL,
  [ResponseTime] int NULL,
  [ResultCode] smallint NULL,
  [PBXServiceIdentity] bigint NULL,
  [Units] int NULL,
  [QueueTime] int NULL,
  [DataSourceCost] money NULL,
  [DataSourcePrice] money NULL,
  [WasPriced] bit NULL,
  [CountryID] int NULL,
  [RawDataID] int NULL,
  [TlrOriginCallID] int NULL,
  [TlrSequenceNumber] int NULL,
  [CostPlanID] int NULL,
  [MaskLocation] nvarchar(30) COLLATE Latin1_General_CI_AS NULL,
  [CostPrecision] real NULL,
  [PricePrecision] real NULL,
  [DataStatusTypeID] int CONSTRAINT [DF_CallsInfo_DataStatus] DEFAULT 1 NOT NULL,
  [CDRID] bigint NULL,
  [OriginalDuration] int NULL,
  [BHours] bit CONSTRAINT [DF_CallsInfo_BHours] DEFAULT 0 NOT NULL,
  [AccountCodeID] int NULL,
  [Descricao da chamada] nvarchar(40) COLLATE Latin1_General_CI_AS NULL,
  [Cidade de destino] nvarchar(25) COLLATE Latin1_General_CI_AS NULL,
  [Codigo DDD] nvarchar(3) COLLATE Latin1_General_CI_AS NULL,
  [Tipo da chamada] nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
  [Tronco] nvarchar(2) COLLATE Latin1_General_CI_AS NULL,
  [MS DE REFERNCIA] nvarchar(4) COLLATE Latin1_General_CI_AS NULL,
  CONSTRAINT [PK_CallsInfo] PRIMARY KEY CLUSTERED ([CallID]),
  CONSTRAINT [FK_CallsInfo_Calls] FOREIGN KEY ([CallID]) 
  REFERENCES [dbo].[Calls] ([CallID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
)
ON [PRIMARY]
GO

    Agradeço antecipadamente,

    Muito obrigado,

    []s, Marco

×
×
  • Criar Novo...