Recreating history based on StartDate/EndDate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recreating history based on StartDate/EndDate

Author

Topic

marcodelphi
Starting Member

4 Posts

Posted - 2010-07-01 : 15:55:59

Hello Guys,

I'm trying to modify a history table, where given a new entry, the table is recreated (only for records with the same ID). Columns DataAtivacao DataDesativacao and all records of the same ID will suffer a "realignment" of their dates. For example, the field DataDesativacao of the previous record should be changed to "DataAtivacao of the current record - 1".

Below is the schema of the database. Than, an example of input / output and also the expected result of what has already tried to solve the problem.

Table Schema:


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

The problem is that I have to keep up the history, I mean, the dates of DataAtivacao and DataDesativacao. Until now, there is no problem, unless I've got to keep the dates "making sense", I mean, when I change the date of DataAtivacao / DataDesativacao of a record, I have to recreate all the other historical containing the same DispositivoID, ie:

The DateFormat is: dd/mm/yyyy

I have the following history, which 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

If DataAtivacao record with ID = 2, is changed to 08/01/2010 and DataDesativacao to 22/01/2010, the result of this change should be the following:


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

I have come across with the following, but no satisfy solution, which consists of trigger that changes the previous associated record. This procedure will change only the first previous record of a given DispositivoID:


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 inves 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 ja existe um historico com o mesmo dispositivo, 
  --entao 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

This procedure works only for the first previous record, which does not solve the problem. The table must be rebuilt according to the input.

I hope I have been happy to expose the problem.

Any help will be appreciated.

Thank you,

Marco André

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-01 : 16:24:29

1. can we assume you want the changes to cascade down (if there are any records downstream) and cascade up (if there are any records upstream)

2. Can we also assume both DataAtivacao and DataDesativacao can change and that triggers the changes described in #1?

3. can DataAtivacao change and not DataDesativacao (vise versa) or they change together? what triggers the change, UI or another process?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

marcodelphi
Starting Member

4 Posts

Posted - 2010-07-01 : 16:39:48

Hi Yosiasz, thanks for your reply.

Lets continue...

1.Correct. All the changes are cascaded down and upstream
2.Correct.
3.Both can change, but not allways at the same time. Changes can be made only in one field or in both fields.

The change is made by an UI.

marcodelphi
Starting Member

4 Posts

Posted - 2010-07-01 : 22:37:43

quote:
Originally posted by marcodelphi

Hi Yosiasz, thanks for your reply.

Lets continue...

1.Correct. All the changes are cascaded down and upstream
2.Correct.
3.Both can change, but not allways at the same time. Changes can be made only in one field or in both fields.

The change is made by an UI.

I have more info about the issue:

Actually none of the rows should be deleted. If I insert a new record, it doesnt matter the date, all the related dates, from the specified DispositivoID should be realigned. That is also true if I update a record.

If I change any of the 2 fields(DataAtivacao/DataDesativacao-StartDate/EndDate) in any record, all related records(from specified ID) should be realigned.

There are no limit to the changes. If there are 1000 records above or below a date, than all the records should be realigned.

All the changes, including new records added, should trigger a procedure that will process all related records downstream and upstream. For instance, taking your example above. Lets suppose a user runs the following queries against the table:


INSERT INTO HistoricoInventario(1,'13/01/2010','17/01/2010');
INSERT INTO HistoricoInventario(1,'18/01/2010','27/01/2010');
UPDATE HistoricoInventario SET StartDate='07/01/2010', EndDate='10/01/2010'
WHERE ID=2;

Here is a list of each statement with the their respective resultset:


Original state:

ID	DispositivoID 		StartDate	EndDate
1	1			01/01/2010	05/01/2010	
2	1			06/01/2010	15/01/2010
3	1			16/01/2010	20/01/2010
4	1			21/01/2010	22/01/2010

INSERT INTO HistoricoInventario(1,'13/01/2010','17/01/2010')

1	1			01/01/2010	05/01/2010	
2	1			06/01/2010	14/01/2010
*5  	1	                13/01/2010	17/01/2010 -- new record(find the gap, and realing all other records)
3	1			18/01/2010	20/01/2010
4	1			21/01/2010	22/01/2010

INSERT INTO HistoricoInventario(1,'18/01/2010','27/01/2010')

1	1			01/01/2010	05/01/2010	
2	1			06/01/2010	13/01/2010
5       1            	        14/01/2010	15/01/2010
3	1			16/01/2010	17/01/2010
*6	1 			18/01/2010	27/01/2010 -- new record(find the gap, and realing all other records)
4	1			28/01/2010	28/01/2010

UPDATE HistoricoInventario SET StartDate='07/01/2010', EndDate='10/01/2010' WHERE ID=2

1	1			01/01/2010	06/01/2010 -- step2,previous record changed(EndDate=StartDate from current record(2) minus 1
*2	1			07/01/2010	10/01/2010 -- step1,record updated
5    	1	         11/01/2010	15/01/2010 -- step3,next record changed(StartDate=DataDesativacao current record(2) plus 1
3	1			16/01/2010	17/01/2010 -- step4,doesnt need to make change, because all the next and previous dates are aligned.
6    	1 			18/01/2010   	27/01/2010 -- step5,same as step4
4	1			28/01/2010	28/01/2010 -- step6,same as step4

I hope you can understand now. If you still have any doubt, please, let me know.

Thank you very much for your help!

Marco André

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-07 : 11:26:08

the only way I can think of doing this is with a sql cursor you loop through. DECLARE cursor_name CURSOR and all. This cursor will be built using the DispositivoID in the WHERE clause excluding the record that changed. (assuming user can change only one record at a time. Is that correct?) SORTED BY DataAtivacao. Then for each row grab last value and update the next record type of thing sounds chungy but might work.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources