Bitemporal Anchor Modeling 1C2R Partitioning

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

-- Partition function for two partitions, 0 and 1
CREATE PARTITION FUNCTION ReliabilityPartition (bit)
AS RANGE LEFT FOR VALUES(0, 1);

GO

-- Partition scheme using the function above
CREATE PARTITION SCHEME ReliabilityScheme
AS PARTITION ReliabilityPartition
ALL TO ([PRIMARY]);

GO

-- 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
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,
    -- Recording time (Anchor Modeling), Transaction time (Snodgrass), Assertion time (Johnston)
    [FI_RAT_RecordedAt] [date] NOT NULL,
    [FI_RAT_ErasedAt] [date] NULL,
    [FI_RAT_Erased] [bit] NOT NULL DEFAULT 0,
    [Metadata_FI_RAT] [int] NOT NULL,
    PRIMARY KEY CLUSTERED (
        [FI_ID] ASC,
        [FI_RAT_ChangedAt] DESC,
        [FI_RAT_RecordedAt] DESC,
        [FI_RAT_Erased] ASC
    ),
    FOREIGN KEY ([FI_ID]) REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
) ON ReliabilityScheme([FI_RAT_Erased]);

GO

-- Time-traveling function of the attribute
CREATE FUNCTION [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
    @changingTimepoint datetime,
    @recordingTimepoint datetime
)
RETURNS TABLE
RETURN
SELECT
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
FROM
    [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
WHERE (
        -- use the whole recorded partition
        [FI_RAT].[FI_RAT_Erased] = 0
    OR (
            -- and in the erased partition
            [FI_RAT].[FI_RAT_Erased] = 1
        AND
            -- search for what was valid at the time
            [FI_RAT].FI_RAT_ErasedAt > @recordingTimepoint
    )
)
AND
   [FI_RAT].FI_RAT_ChangedAt <= @changingTimepoint
AND
   [FI_RAT].FI_RAT_RecordedAt <= @recordingTimepoint;

GO

-- Insert trigger on the table
CREATE TRIGGER [dbo].[itFI_RAT_FinancialInstrument_Rating] ON [dbo].[FI_RAT_FinancialInstrument_Rating]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE [FI_RAT]
SET
    FI_RAT_ErasedAt = ISNULL(i.[FI_RAT_ErasedAt], getdate()), -- DW/OLTP
    FI_RAT_Erased = 1
FROM
    [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
JOIN
    inserted i
ON
    [FI_RAT].FI_ID = i.FI_ID
AND
    [FI_RAT].FI_RAT_ChangedAt = i.FI_RAT_ChangedAt
WHERE
    i.[FI_RAT_Erased] = 1
AND
    [FI_RAT].FI_RAT_Erased = 0;

INSERT INTO [dbo].[FI_RAT_FinancialInstrument_Rating] (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
)
SELECT
    i.[FI_ID],
    i.[FI_RAT_FinancialInstrument_Rating],
    ISNULL(i.[FI_RAT_ChangedAt], getdate()), -- DW/OLTP
    ISNULL(i.[FI_RAT_RecordedAt], getdate()), -- DW/OLTP
    null,
    ISNULL(i.[FI_RAT_Erased], 0),
    i.[Metadata_FI_RAT]
FROM
    inserted i
WHERE
    i.FI_RAT_Erased is null
OR
    i.FI_RAT_Erased = 0;
END

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
CREATE VIEW [dbo].[llFI_FinancialInstrument]
AS
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].FI_RAT_Erased,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
   [FI_RAT].FI_RAT_Erased = 0 -- select partition
AND
   [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_RAT_Erased = 0
      AND
         sub.FI_ID = [FI].FI_ID
    );

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,
   [FI_RAT].FI_RAT_RecordedAt,
   [FI_RAT].FI_RAT_Erased,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
   [FI_RAT].FI_RAT_Erased = 0 -- select partition
AND
   [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_RAT_Erased = 0
      AND
         sub.FI_ID = [FI].FI_ID
      AND
         sub.FI_RAT_ChangedAt <= @timepoint
    );

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
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [FI_RAT].FI_RAT_RecordedAt,
   [FI_RAT].FI_RAT_ErasedAt,
   [FI_RAT].FI_RAT_Erased,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
      @changingTimepoint,
      @recordingTimepoint
   ) [FI_RAT]
ON
   [FI_RAT].FI_ID = [FI].FI_ID
AND
   [FI_RAT].FI_RAT_ChangedAt = (
      SELECT
         max(sub.FI_RAT_ChangedAt)
      FROM
         [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
            @changingTimepoint,
            @recordingTimepoint
         ) sub
      WHERE
         sub.FI_ID = [FI_RAT].FI_ID
    );

GO

-- create one financial instrument
delete from FI_FinancialInstrument;
insert into FI_FinancialInstrument (Metadata_FI) values (42);

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

-- DAY 1
-- insert the first ratings for the created financial instrument
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'A', '2011-03-17', '2011-03-17', 42);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, '?', '2011-03-19', '2011-03-17', 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
-- We need to remove what was wrong before we can insert the correction
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (1, '2011-03-17', '2011-03-17', '2011-03-18', 1, 43);

insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'B', '2011-03-16', '2011-03-18', 43);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'A', '2011-03-18', '2011-03-18', 43);

-- 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 FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (1, '2011-03-16', '2011-03-18', '2011-03-19', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (1, '2011-03-18', '2011-03-18', '2011-03-19', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (1, '2011-03-19', '2011-03-17', '2011-03-19', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'C', '2011-03-15', '2011-03-19', 44);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, '?', '2011-03-20', '2011-03-19', 44);

-- 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');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');

-- DAY 4
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (1, '2011-03-20', '2011-03-19', '2011-03-20', 1, 45);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'D', '2011-03-17', '2011-03-20', 45);

-- 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');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-20');


-- DAY 5
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'E', '2011-03-18', '2011-03-21', 46);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (1, 'D', '2011-03-20', '2011-03-21', 46);

-- 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-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');

/* Our own simple test

Event 1, Arriving at 2005-09-10:
- The rating of the instrument is 'A' from 2001-01-01.
Event 2, Arriving at 2005-10-12:
- The rating of the instrument was actually 'B' from 2001-01-01.
Event 3, Arriving at 2005-10-20:
- The rating of the instrument has been set to 'F' from 2003-02-13.
Event 4, Arriving at 2005-11-09:
- The rating of the instrument was set to 'D' at 2002-04-10.
Event 5, Also arriving at 2005-11-09:
- The rating of the instrument was then changed again to 'E' at 2002-08-20.
Event 6, Arriving at 2006-09-21:
- The rating was never set to 'F', it was an error.

*/

-- create another financial instrument
insert into FI_FinancialInstrument (Metadata_FI) values (42);

-- EVENT 1
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (2, 'A', '2001-01-01', '2005-09-10', 555);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before and after
select * from plFI_FinancialInstrument('2000-12-31');
select * from plFI_FinancialInstrument('2001-01-02');


-- EVENT 2
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (2, '2001-01-01', '2005-10-12', 1, 556);
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (2, 'B', '2001-01-01', '2005-10-12', 556);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before and after
select * from plFI_FinancialInstrument('2000-12-31');
select * from plFI_FinancialInstrument('2001-01-02');
-- before and after in retrospect
select * from ppFI_FinancialInstrument('2000-12-31', '2005-09-22');
select * from ppFI_FinancialInstrument('2001-01-02', '2005-09-22');


-- EVENT 3
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (2, 'F', '2003-02-13', '2005-10-20', 557);

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


-- EVENT 4
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (2, 'D', '2002-04-10', '2005-11-09', 558);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before
select * from plFI_FinancialInstrument('2003-02-12');


-- EVENT 5
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
) values (2, 'E', '2002-08-20', '2005-11-09', 559);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before
select * from plFI_FinancialInstrument('2003-02-12');


-- EVENT 6
insert into FI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Erased],
    [Metadata_FI_RAT]
) values (2, '2003-02-13', '2006-09-21', 1, 557);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before
select * from plFI_FinancialInstrument('2003-02-12');
-- latest in retrospect
select * from ppFI_FinancialInstrument(GETDATE(), '2006-09-20');
select * from ppFI_FinancialInstrument(GETDATE(), '2005-10-12');