Bi-temporal Anchor Modeling by Bas van den Berg

/*
 Simple Bitemporal Test (using Anchor Modeling)
 http://www.temporaldata.com/wp-content/uploads/2011/04/bitemporal-support-for-a-temporal-example.swf
*/

-- Anchor table
CREATE TABLE [dbo].[FI_FinancialInstrument] (
    [FI_ID] [int] IDENTITY(1,1) NOT NULL,
    [Metadata_FI] [int] NOT NULL,
    PRIMARY KEY CLUSTERED (
        [FI_ID] ASC
    )
);

GO

-- Attribute table
--drop table [dbo].[FI_RAT_FinancialInstrument_Rating]
CREATE TABLE [dbo].[FI_RAT_FinancialInstrument_Rating] (
    [FI_ID] [int] NOT NULL,
    [FI_RAT_FinancialInstrument_Rating] [char](1) NOT NULL,
    -- Changing time (Anchor Modeling),  Valid time (Snodgrass), Effective time (Johnston)
    [FI_RAT_ChangedAt] [date] NOT NULL,
    [FI_RAT_IsValid] [bit] NOT NULL,
    [Metadata_FI_RAT] [int] NOT NULL,
    PRIMARY KEY CLUSTERED (
        [FI_ID] ASC,
        [FI_RAT_ChangedAt] DESC
    ),
--  FOREIGN KEY ([FI_ID]) REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
);


-- Attribute table
-- drop table [dbo].[HISTORY_FI_RAT_FinancialInstrument_Rating]
CREATE TABLE [dbo].[HISTORY_FI_RAT_FinancialInstrument_Rating](
    [FI_ID] [int] NOT NULL,
    [FI_RAT_FinancialInstrument_Rating] [char](1) NOT NULL,
    -- Changing time (Anchor Modeling),  Valid time (Snodgrass), Effective time (Johnston)
    [FI_RAT_ChangedAt] [date] NOT NULL,
-- Recording time (Anchor Modeling), Transaction time (Snodgrass), Assertion time (Johnston)
    [FI_RAT_RecordedAt] [date] NOT NULL,
    [FI_RAT_IsValid] [bit] NOT NULL,
    [Metadata_FI_RAT] [int] NOT NULL,
    PRIMARY KEY CLUSTERED (
        [FI_ID] ASC,
        [FI_RAT_ChangedAt] DESC,
        [FI_RAT_RecordedAt] DESC
    ),
--  FOREIGN KEY ([FI_ID]) REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
);


GO

-- Meta date Anchor table
CREATE TABLE [dbo].[MD_MetaData] (
    [MD_ID] [int] NOT NULL,
    [MD_RecordedAt] datetime NOT NULL
    PRIMARY KEY CLUSTERED (
        [MD_ID] ASC
    )
);

GO

/*
    There are nine 'perspectives' of the information resulting
    from latest, point-in-time, and interval perspectives
    taken over the two timelines (changing and recording).
    I will show three of them here.
*/

-- latest for changing time and latest for recording time
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[llFI_FinancialInstrument]'))
DROP VIEW [dbo].[llFI_FinancialInstrument]
GO

CREATE VIEW [dbo].[llFI_FinancialInstrument]
AS
SELECT
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [MD].MD_RecordedAt FI_RAT_RecordedAt,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
   [FI_RAT].FI_ID = [FI].FI_ID
AND
   [FI_RAT].FI_RAT_ChangedAt = (
      SELECT
         max(sub.FI_RAT_ChangedAt)
      FROM
         [dbo].[FI_RAT_FinancialInstrument_Rating] sub
      WHERE
         sub.FI_ID = [FI].FI_ID
/*
      AND -- latest reliability equals 1 can be removed because unreliable data is moved to HISTORY table
        (
            select top 1
                rel.FI_RAT_Reliability
            from
                [dbo].[FI_RAT_FinancialInstrument_Rating] rel
            where
                rel.FI_ID = [FI].FI_ID
            and
                rel.FI_RAT_ChangedAt = sub.FI_RAT_ChangedAt
            order by
                rel.FI_RAT_RecordedAt desc
         ) > 0 */
    )
/*
AND
   [FI_RAT].FI_RAT_RecordedAt = (
        SELECT
            MAX(sub.FI_RAT_RecordedAt)
        FROM
            [dbo].[FI_RAT_FinancialInstrument_Rating] sub
        WHERE
            sub.FI_ID = [FI_RAT].FI_ID
        AND
            sub.FI_RAT_ChangedAt = [FI_RAT].FI_RAT_ChangedAt
    );
*/
LEFT JOIN
    dbo.MD_MetaData MD on MD.MD_ID = FI_RAT.Metadata_FI_RAT

GO

-- point-in-time for changing time and latest for recording time
CREATE FUNCTION [dbo].[plFI_FinancialInstrument] (
    @timepoint datetime
)
RETURNS TABLE
RETURN
SELECT
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [MD].MD_RecordedAt FI_RAT_RecordedAt,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
   [FI_RAT].FI_ID = [FI].FI_ID
AND
   [FI_RAT].FI_RAT_ChangedAt = (
      SELECT
         max(sub.FI_RAT_ChangedAt)
      FROM
         [dbo].[FI_RAT_FinancialInstrument_Rating] sub
      WHERE
         sub.FI_ID = [FI].FI_ID
      AND
         sub.FI_RAT_ChangedAt <= @timepoint
    )
    LEFT JOIN
    dbo.MD_MetaData MD on MD.MD_ID = FI_RAT.Metadata_FI_RAT

GO

/****** Object:  View [dbo].[h_FI_RAT_FinancialInstrument_Rating]    Script Date: 10/25/2011 19:28:22 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[h_FI_RAT_FinancialInstrument_Rating]'))
DROP VIEW [dbo].[h_FI_RAT_FinancialInstrument_Rating]
GO

CREATE VIEW [dbo].[h_FI_RAT_FinancialInstrument_Rating] as
select [FI_ID]
      ,[FI_RAT_FinancialInstrument_Rating]
      ,[FI_RAT_ChangedAt]
      ,[FI_RAT_RecordedAt]
      ,[FI_RAT_IsValid]
      , 1 FI_RAT_Retracted
      ,[Metadata_FI_RAT]
      ,MD.MD_RecordedAt retractedAt
      FROM [AM_Fin_instrument2].[dbo].[HISTORY_FI_RAT_FinancialInstrument_Rating]
      inner join MD_MetaData MD on [Metadata_FI_RAT] = MD.MD_ID
      UNION ALL
      SELECT [FI_ID]
      ,[FI_RAT_FinancialInstrument_Rating]
      ,[FI_RAT_ChangedAt]
      ,[MD].MD_RecordedAt FI_RAT_RecordedAt
      ,[FI_RAT_IsValid]
      , 0 FI_RAT_Retracted
      ,[Metadata_FI_RAT]
      , null retractedAt
        FROM   [dbo].[FI_RAT_FinancialInstrument_Rating] FI_RAT2
        LEFT JOIN dbo.MD_MetaData MD on MD.MD_ID = FI_RAT2.Metadata_FI_RAT

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ppFI_FinancialInstrument]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ppFI_FinancialInstrument]
GO

-- point-in-time for changing time and point-in-time for recording time
CREATE FUNCTION [dbo].[ppFI_FinancialInstrument] (
    @changingTimepoint datetime,
    @recordingTimepoint datetime
)
RETURNS TABLE
RETURN

select * from (
SELECT
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [FI_RAT].FI_RAT_RecordedAt,
   [FI_RAT].Metadata_FI_RAT
   , row_number() over (partition by FI_RAT.FI_ID order by  FI_RAT_ChangedAt Desc) seq_ChangedAt
   , row_number() over (partition by FI_RAT.FI_ID, FI_RAT_ChangedAt order by  FI_RAT_RecordedAt Desc) seq_RecordedAt
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[h_FI_RAT_FinancialInstrument_Rating] [FI_RAT] -- use history view, because we also consider retracted values
ON
   [FI_RAT].FI_ID = [FI].FI_ID
WHERE
      FI_RAT_RecordedAt <= @recordingTimepoint
  AND (retractedAt is NULL OR retractedAt > @recordingTimepoint)
  AND FI_RAT_ChangedAt <= @changingTimepoint
) q
where seq_ChangedAt=1 and seq_RecordedAt=1


GO
*/
-- =============================================
-- Author:      Bas van den Berg
-- Create date: 2011-11-03
-- Description: This will retract a record. i.e. move it to the HISTORY table and then delete the source
-- =============================================
CREATE PROCEDURE retract
    -- Add the parameters for the stored procedure here
    @tableName varchar(200)
,   @anchorKey bigint
,   @changedAt datetime = NULL  -- optional
,   @MD_ID int -- new metadata id
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    -- TODO make this proc generic.
    INSERT INTO [dbo].[HISTORY_FI_RAT_FinancialInstrument_Rating]
    (
            [FI_ID]
           ,[FI_RAT_FinancialInstrument_Rating]
           ,[FI_RAT_ChangedAt]
           ,[FI_RAT_RecordedAt]
           ,[FI_RAT_IsValid]
           ,[Metadata_FI_RAT])
    SELECT [FI_ID]
          ,[FI_RAT_FinancialInstrument_Rating]
          ,[FI_RAT_ChangedAt]
          , m.MD_RecordedAt -- original recordedAt
          ,FI_RAT_IsValid
          ,@MD_ID
    FROM [dbo].[FI_RAT_FinancialInstrument_Rating] r
    INNER JOIN MD_MetaData m on m.MD_ID = r.Metadata_FI_RAT
    WHERE r.FI_ID = @anchorKey
        and r.FI_RAT_ChangedAt = @changedAt

    DELETE FROM FI_RAT_FinancialInstrument_Rating
    WHERE FI_ID = @anchorKey
        and FI_RAT_ChangedAt = @changedAt

    COMMIT TRANSACTION

END
GO

-- create one financial instrument
truncate table  [FI_FinancialInstrument]
truncate table  FI_RAT_FinancialInstrument_Rating
truncate table  HISTORY_FI_RAT_FinancialInstrument_Rating
truncate table MD_MetaData

insert into MD_MetaData values(41, '2011-03-17')
insert into FI_FinancialInstrument (Metadata_FI) values (41);


-- we should have no information whatsoever yet
select * from FI_RAT_FinancialInstrument_Rating;
select * from llFI_FinancialInstrument;

-- DAY 1
-- insert the first ratings for the created financial instrument
insert into MD_MetaData values(42, '2011-03-17')
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'A', '2011-03-17', 1, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, '?', '2011-03-19', 1, 42);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;

-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');

-- DAY 2
-- Note that the following requires user knowledge of existing data
-- A rating become B rating starting from 03-16
-- first retract old A rating
insert into MD_MetaData values(43, '2011-03-18')

exec retract 'FI_RAT_FinancialInstrument_Rating', 1,  '2011-03-17' , 43

select * from h_FI_RAT_FinancialInstrument_Rating

insert into FI_RAT_FinancialInstrument_Rating
values (1, 'B', '2011-03-16', 1, 43);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'A', '2011-03-18', 1, 43);

/* It may be possible to do:

insert into FI_RAT_FinancialInstrument_Rating
values (1, 'B', '[2011-03-16, 2011-03-17]', 2011-03-18, 42);

using an insert trigger, and derive the three inserts above.
*/

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');

-- DAY 3
insert into MD_MetaData values(44, '2011-03-19')

exec retract 'FI_RAT_FinancialInstrument_Rating', 1, '2011-03-16',44
exec retract 'FI_RAT_FinancialInstrument_Rating', 1, '2011-03-18',44
exec retract 'FI_RAT_FinancialInstrument_Rating', 1, '2011-03-19',44
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'C', '2011-03-15', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating
values (1, '?', '2011-03-20', 1, 44);

-- all rows in the attribute table
select * from h_FI_RAT_FinancialInstrument_Rating order by 4;
select * from FI_RAT_FinancialInstrument_Rating order by 4;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');

-- DAY 4
insert into MD_MetaData values(45, '2011-03-20')

exec retract 'FI_RAT_FinancialInstrument_Rating', 1, '2011-03-20',45

insert into FI_RAT_FinancialInstrument_Rating
values (1, 'D', '2011-03-17',  1, 45);

-- all rows in the attribute table
select * from h_FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-20');


-- DAY 5
insert into MD_MetaData values(46, '2011-03-21')

insert into FI_RAT_FinancialInstrument_Rating
values (1, 'E', '2011-03-18',  1, 46);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'D', '2011-03-20',  1, 46);

-- all rows in the attribute table
select * from h_FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-18', '2011-03-20');
select * from ppFI_FinancialInstrument('2011-03-18', '2011-03-21');
select * from ppFI_FinancialInstrument('2011-03-19', '2011-03-20');
select * from ppFI_FinancialInstrument('2011-03-19', '2011-03-21');