Bitemporal Anchor Modeling 1C2R Two Tables

/*
 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 (keeping the currently recorded information)
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,
    [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])
);

GO

-- Attribute table (keeping the erased, previously recorded information)
CREATE TABLE [dbo].[eFI_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] 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

-- This view combines recorded and erased information and adds a
-- computed reliability bit, which we shall also use for 'deletes'
CREATE VIEW [dbo].[tFI_RAT_FinancialInstrument_Rating]
AS
SELECT
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    null as [FI_RAT_ErasedAt],
    1 as FI_RAT_Reliability,
    [Metadata_FI_RAT]
FROM
    [dbo].[FI_RAT_FinancialInstrument_Rating]
UNION ALL
SELECT
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    0 as FI_RAT_Reliability,
    [Metadata_FI_RAT]
FROM
    [dbo].[eFI_RAT_FinancialInstrument_Rating];

GO

-- Insert trigger on the view
-- If the reliability bit is 1 then its a regular insert
-- If the reliability bit is 0 then its a logical delete
CREATE TRIGGER [dbo].[itFI_RAT_FinancialInstrument_Rating] ON [dbo].[tFI_RAT_FinancialInstrument_Rating]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[FI_RAT_FinancialInstrument_Rating] (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
)
SELECT
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [Metadata_FI_RAT]
FROM
    inserted
WHERE
    FI_RAT_Reliability = 1;

INSERT INTO [dbo].[eFI_RAT_FinancialInstrument_Rating] (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_ErasedAt],
    [Metadata_FI_RAT]
)
SELECT
    [FI_RAT].[FI_ID],
    [FI_RAT].[FI_RAT_FinancialInstrument_Rating],
    [FI_RAT].[FI_RAT_ChangedAt],
    [FI_RAT].[FI_RAT_RecordedAt],
    ISNULL(i.[FI_RAT_ErasedAt], getdate()), -- DW/OLTP
    i.[Metadata_FI_RAT]
FROM
    inserted i
JOIN
    [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
    i.FI_ID = [FI_RAT].FI_ID
AND
    i.FI_RAT_ChangedAt = [FI_RAT].FI_RAT_ChangedAt
WHERE
    i.FI_RAT_Reliability = 0;

DELETE [FI_RAT]
FROM
    [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
JOIN
    inserted i
ON
    i.FI_ID = [FI_RAT].FI_ID
AND
    i.FI_RAT_ChangedAt = [FI_RAT].FI_RAT_ChangedAt
WHERE
    i.FI_RAT_Reliability = 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].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
    );

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].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
    );

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,
   case
        when [FI_RAT].FI_ID is null
            then [eFI_RAT].FI_RAT_FinancialInstrument_Rating
        when [eFI_RAT].FI_ID is null
            then [FI_RAT].FI_RAT_FinancialInstrument_Rating
        when [FI_RAT].FI_RAT_ChangedAt > [eFI_RAT].FI_RAT_ChangedAt
            then [FI_RAT].FI_RAT_FinancialInstrument_Rating
        when [FI_RAT].FI_RAT_ChangedAt < [eFI_RAT].FI_RAT_ChangedAt
            then [eFI_RAT].FI_RAT_FinancialInstrument_Rating
        when [FI_RAT].FI_RAT_RecordedAt >= [eFI_RAT].FI_RAT_RecordedAt
            then [FI_RAT].FI_RAT_FinancialInstrument_Rating
        else [eFI_RAT].FI_RAT_FinancialInstrument_Rating
    end as FI_RAT_FinancialInstrument_Rating,
   case
        when [FI_RAT].FI_ID is null
            then [eFI_RAT].FI_RAT_ChangedAt
        when [eFI_RAT].FI_ID is null
            then [FI_RAT].FI_RAT_ChangedAt
        when [FI_RAT].FI_RAT_ChangedAt > [eFI_RAT].FI_RAT_ChangedAt
            then [FI_RAT].FI_RAT_ChangedAt
        when [FI_RAT].FI_RAT_ChangedAt < [eFI_RAT].FI_RAT_ChangedAt
            then [eFI_RAT].FI_RAT_ChangedAt
        when [FI_RAT].FI_RAT_RecordedAt >= [eFI_RAT].FI_RAT_RecordedAt
            then [FI_RAT].FI_RAT_ChangedAt
        else [eFI_RAT].FI_RAT_ChangedAt
    end as FI_RAT_ChangedAt,
   case
        when [FI_RAT].FI_ID is null
            then [eFI_RAT].FI_RAT_RecordedAt
        when [eFI_RAT].FI_ID is null
            then [FI_RAT].FI_RAT_RecordedAt
        when [FI_RAT].FI_RAT_ChangedAt > [eFI_RAT].FI_RAT_ChangedAt
            then [FI_RAT].FI_RAT_RecordedAt
        when [FI_RAT].FI_RAT_ChangedAt < [eFI_RAT].FI_RAT_ChangedAt
            then [eFI_RAT].FI_RAT_RecordedAt
        when [FI_RAT].FI_RAT_RecordedAt >= [eFI_RAT].FI_RAT_RecordedAt
            then [FI_RAT].FI_RAT_RecordedAt
        else [eFI_RAT].FI_RAT_RecordedAt
    end as FI_RAT_RecordedAt,
   case
        when [FI_RAT].FI_ID is null
            then [eFI_RAT].FI_RAT_ErasedAt
        when [eFI_RAT].FI_ID is null
            then null
        when [FI_RAT].FI_RAT_ChangedAt > [eFI_RAT].FI_RAT_ChangedAt
            then null
        when [FI_RAT].FI_RAT_ChangedAt < [eFI_RAT].FI_RAT_ChangedAt
            then [eFI_RAT].FI_RAT_ErasedAt
        when [FI_RAT].FI_RAT_RecordedAt >= [eFI_RAT].FI_RAT_RecordedAt
            then null
        else [eFI_RAT].FI_RAT_ErasedAt
    end as FI_RAT_ErasedAt,
   case
        when [FI_RAT].FI_ID is null
            then [eFI_RAT].Metadata_FI_RAT
        when [eFI_RAT].FI_ID is null
            then [FI_RAT].Metadata_FI_RAT
        when [FI_RAT].FI_RAT_ChangedAt > [eFI_RAT].FI_RAT_ChangedAt
            then [FI_RAT].Metadata_FI_RAT
        when [FI_RAT].FI_RAT_ChangedAt < [eFI_RAT].FI_RAT_ChangedAt
            then [eFI_RAT].Metadata_FI_RAT
        when [FI_RAT].FI_RAT_RecordedAt >= [eFI_RAT].FI_RAT_RecordedAt
            then [FI_RAT].Metadata_FI_RAT
        else [eFI_RAT].Metadata_FI_RAT
    end as 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 <= @changingTimepoint
      AND
         sub.FI_RAT_RecordedAt <= @recordingTimepoint
    )
LEFT JOIN
   [dbo].[eFI_RAT_FinancialInstrument_Rating] [eFI_RAT]
ON
   [eFI_RAT].FI_ID = [FI].FI_ID
AND
   [eFI_RAT].FI_RAT_RecordedAt = (
        SELECT
            max(sub.FI_RAT_RecordedAt)
        FROM
            [dbo].[eFI_RAT_FinancialInstrument_Rating] sub
        WHERE
            sub.FI_ID = [FI].FI_ID
        AND
            sub.FI_RAT_RecordedAt <= @recordingTimepoint
        AND
            sub.FI_RAT_ErasedAt >= @recordingTimepoint
    )
AND
    [eFI_RAT].FI_RAT_ChangedAt = (
        SELECT
            max(sub.FI_RAT_ChangedAt)
        FROM
            [dbo].[eFI_RAT_FinancialInstrument_Rating] sub
        WHERE
            sub.FI_ID = [eFI_RAT].FI_ID
        AND
            sub.FI_RAT_RecordedAt = [eFI_RAT].FI_RAT_RecordedAt
        AND
            sub.FI_RAT_ChangedAt <= @changingTimepoint
    );

GO

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

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

-- DAY 1
-- insert the first ratings for the created financial instrument
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, 'A', '2011-03-17', '2011-03-17', 1, 42);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '?', '2011-03-19', '2011-03-17', 1, 42);

-- all rows in the attribute tables
select * from tFI_RAT_FinancialInstrument_Rating;
-- looking at the split between recorded and erased
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '2011-03-17', '2011-03-18', 0, 43);

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

-- all rows in the attribute tables
select * from tFI_RAT_FinancialInstrument_Rating;
-- looking at the split between recorded and erased
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '2011-03-16', '2011-03-19', 0, 44);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '2011-03-18', '2011-03-19', 0, 44);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '2011-03-19', '2011-03-19', 0, 44);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, 'C', '2011-03-15', '2011-03-19', 1, 44);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '?', '2011-03-20', '2011-03-19', 1, 44);

-- all rows in the attribute tables
select * from tFI_RAT_FinancialInstrument_Rating;
-- looking at the split between recorded and erased
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, '2011-03-20', '2011-03-20', 0, 45);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, 'D', '2011-03-17', '2011-03-20', 1, 45);

-- all rows in the attribute tables
select * from tFI_RAT_FinancialInstrument_Rating;
-- looking at the split between recorded and erased
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, 'E', '2011-03-18', '2011-03-21', 1, 46);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (1, 'D', '2011-03-20', '2011-03-21', 1, 46);

-- all rows in the attribute tables
select * from tFI_RAT_FinancialInstrument_Rating;
-- looking at the split between recorded and erased
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (2, 'A', '2001-01-01', '2005-09-10', 1, 555);

-- all rows in the attribute table
select * from tFI_RAT_FinancialInstrument_Rating;
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_ChangedAt],
    [FI_RAT_ErasedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (2, '2001-01-01', '2005-10-12', 0, 556);
insert into tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (2, 'B', '2001-01-01', '2005-10-12', 1, 556);

-- all rows in the attribute table
select * from tFI_RAT_FinancialInstrument_Rating;
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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 tFI_RAT_FinancialInstrument_Rating (
    [FI_ID],
    [FI_RAT_FinancialInstrument_Rating],
    [FI_RAT_ChangedAt],
    [FI_RAT_RecordedAt],
    [FI_RAT_Reliability],
    [Metadata_FI_RAT]
) values (2, 'F', '2003-02-13', '2005-10-20', 1, 557);

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


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

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


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

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


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

-- all rows in the attribute table
select * from tFI_RAT_FinancialInstrument_Rating;
select * from FI_RAT_FinancialInstrument_Rating;
select * from eFI_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');