Bitemporal Anchor Modeling 1C1R One Table

/*
 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
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_Reliability] [tinyint] NOT NULL,
    [Metadata_FI_RAT] [int] NOT NULL,
    PRIMARY KEY CLUSTERED (
        [FI_ID] ASC,
        [FI_RAT_ChangedAt] DESC,
        [FI_RAT_RecordedAt] DESC,
        [FI_RAT_Reliability] DESC
    ),
    FOREIGN KEY ([FI_ID]) REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
);

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_Reliability,
   [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
        (
            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,
                rel.FI_RAT_Reliability 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
    )
AND
    [FI_RAT].FI_RAT_Reliability = (
        SELECT
            MAX(sub.FI_RAT_Reliability)
        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
        AND
            sub.FI_RAT_RecordedAt = [FI_RAT].FI_RAT_RecordedAt
    );

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_Reliability,
   [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
      AND
        (
            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,
                rel.FI_RAT_Reliability 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
    )
AND
    [FI_RAT].FI_RAT_Reliability = (
        SELECT
            MAX(sub.FI_RAT_Reliability)
        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
        AND
            sub.FI_RAT_RecordedAt = [FI_RAT].FI_RAT_RecordedAt
    );

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_Reliability,
   [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 <= @changingTimepoint
      AND
        (
            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
            and
                rel.FI_RAT_RecordedAt <= @recordingTimepoint
            order by
                rel.FI_RAT_RecordedAt desc,
                rel.FI_RAT_Reliability 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
        AND
            sub.FI_RAT_RecordedAt <= @recordingTimepoint
    )
AND
    [FI_RAT].FI_RAT_Reliability = (
        SELECT
            MAX(sub.FI_RAT_Reliability)
        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
        AND
            sub.FI_RAT_RecordedAt = [FI_RAT].FI_RAT_RecordedAt
    );


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;
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
values (1, 'A', '2011-03-17', '2011-03-17', 1, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, '?', '2011-03-19', '2011-03-17', 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
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'A', '2011-03-17', '2011-03-18', 0, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'B', '2011-03-16', '2011-03-18', 1, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'A', '2011-03-18', '2011-03-18', 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');
-- 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
values (1, 'B', '2011-03-16', '2011-03-19', 0, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'A', '2011-03-18', '2011-03-19', 0, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, '?', '2011-03-19', '2011-03-19', 0, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'C', '2011-03-15', '2011-03-19', 1, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, '?', '2011-03-20', '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');
-- 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
values (1, '?', '2011-03-20', '2011-03-20', 0, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'D', '2011-03-17', '2011-03-20', 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');
-- 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
values (1, 'E', '2011-03-18', '2011-03-21', 1, 42);
insert into FI_RAT_FinancialInstrument_Rating
values (1, 'D', '2011-03-20', '2011-03-21', 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');
-- 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
values (2, 'A', '2001-01-01', '2005-09-10', 1, 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
values (2, 'A', '2001-01-01', '2005-10-12', 0, 556);
insert into FI_RAT_FinancialInstrument_Rating
values (2, 'B', '2001-01-01', '2005-10-12', 1, 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
values (2, 'F', '2003-02-13', '2005-10-20', 1, 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
values (2, 'D', '2002-04-10', '2005-11-09', 1, 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
values (2, 'E', '2002-08-20', '2005-11-09', 1, 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
values (2, 'F', '2003-02-13', '2006-09-21', 0, 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');