Concurrent-reliance-temporal

/*
      A concurrent-reliance-temporal implementation of Anchor Modeling
      by Lars Rönnbäck, November 2012

      http://www.anchormodeling.com
 */

---------------- CREATE THE IMPLEMENTATION -------------------

SELECT @@version;

DROP VIEW [dbo].[llFI_FinancialInstrument];
DROP FUNCTION [dbo].[llrFI_FinancialInstrument];
DROP FUNCTION [dbo].[plFI_FinancialInstrument];
DROP FUNCTION [dbo].[lpFI_FinancialInstrument];
DROP FUNCTION [dbo].[ppFI_FinancialInstrument];
DROP FUNCTION [dbo].[pvpvrFI_FinancialInstrument];
DROP FUNCTION [dbo].[rFI_RAT_FinancialInstrument_Rating];
DROP FUNCTION [dbo].[rFI_RAT_FinancialInstrument_Rating_Posit];
DROP FUNCTION [dbo].[rFI_RAT_FinancialInstrument_Rating_Annex];
DROP VIEW [dbo].[FI_RAT_FinancialInstrument_Rating];
DROP TABLE [dbo].[FI_RAT_FinancialInstrument_Rating_Annex];
DROP TABLE [dbo].[FI_RAT_FinancialInstrument_Rating_Posit];
DROP TABLE [dbo].[FI_FinancialInstrument];
DROP FUNCTION [dbo].[vFI_RAT_FinancialInstrument_Rating];

/* -------------------------- Anchor --------------------------
 * Holds identities of entities in the domain.
 *
 */
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 Posit ----------------------
 * A statement binding a property value to an identity,
 * for every point in changing time.
 *
 *    A_p: I x T_c -> D
 */
CREATE TABLE [dbo].[FI_RAT_FinancialInstrument_Rating_Posit] (
   [FI_RAT_ID] [int] IDENTITY(1,1) NOT NULL,
   [FI_ID] [int] NOT NULL,
   [FI_RAT_FinancialInstrument_Rating] [char](1) NOT NULL,
   -- Changing time (Anchor Modeling)
   -- Valid time (Snodgrass)
   -- Effective time (Johnston)
   -- Application time (SQL:2011)
   [FI_RAT_ChangedAt] [date] NOT NULL,
   CONSTRAINT [pkFI_RAT_FinancialInstrument_Rating_Posit]
   PRIMARY KEY NONCLUSTERED (
      [FI_RAT_ID] ASC
   ),
   CONSTRAINT [uqFI_RAT_FinancialInstrument_Rating_Posit]
   UNIQUE CLUSTERED (
      [FI_ID] ASC,
      [FI_RAT_ChangedAt] DESC,
      [FI_RAT_FinancialInstrument_Rating]
   ),
   FOREIGN KEY ([FI_ID])
   REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
);

GO
/* --------------------- Attribute Annex ----------------------
 * A statement binding a posit to its metadata, in this case
 * an agent's assessment of its reliability,
 * for every point in positing time.
 *
 *   A_a: I x T_r -> (A, R)
 */

CREATE TABLE [dbo].[FI_RAT_FinancialInstrument_Rating_Annex] (
   [FI_RAT_ID] [int] NOT NULL,
   -- Positing time (Anchor Modeling)
   -- Transaction time (Snodgrass)
   -- Assertion time (Johnston)
   -- System versioned time (SQL:2011)
   [FI_RAT_PositedAt] [date] NOT NULL,
   -- make the following data type settable in the tool
   [FI_RAT_Positor] [tinyint] NOT NULL,
   -- make the following data type settable in the tool
   [FI_RAT_Reliability] [tinyint] NOT NULL,
   [Metadata_FI_RAT] [int] NOT NULL,
   [FI_RAT_Reliable] AS CAST(
   CASE
      -- make this cutoff settable in the tool
      WHEN [FI_RAT_Reliability] <= 0 THEN 0
      ELSE 1
   END AS [bit]),
   CONSTRAINT [pkFI_RAT_FinancialInstrument_Rating_Annex]
   PRIMARY KEY CLUSTERED (
      [FI_RAT_ID] ASC,
      [FI_RAT_Positor] ASC,
      [FI_RAT_PositedAt] DESC
   ),
   FOREIGN KEY ([FI_RAT_ID])
   REFERENCES [dbo].[FI_RAT_FinancialInstrument_Rating_Posit] ([FI_RAT_ID])
);

GO

-- Create an assembled view of the attribute
CREATE VIEW [dbo].[FI_RAT_FinancialInstrument_Rating]
WITH SCHEMABINDING
AS
SELECT
   [FI_RAT_p].[FI_ID],
   [FI_RAT_p].[FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_p].[FI_RAT_ChangedAt],
   [FI_RAT_a].[FI_RAT_PositedAt],
   [FI_RAT_a].[FI_RAT_Positor],
   [FI_RAT_a].[FI_RAT_Reliability],
   [FI_RAT_p].[FI_RAT_ID],
   [FI_RAT_a].[Metadata_FI_RAT],
   [FI_RAT_a].[FI_RAT_Reliable]
FROM
   [dbo].[FI_RAT_FinancialInstrument_Rating_Posit] [FI_RAT_p]
JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating_Annex] [FI_RAT_a]
ON
   [FI_RAT_a].FI_RAT_ID = [FI_RAT_p].FI_RAT_ID;

GO

-- Constraint ensuring that recorded and erased posits are temporally consistent
CREATE UNIQUE CLUSTERED INDEX [pkFI_RAT_FinancialInstrument_Rating]
ON [dbo].[FI_RAT_FinancialInstrument_Rating](
   [FI_RAT_Positor] ASC,
   [FI_RAT_Reliable] DESC,
   [FI_ID] ASC,
   [FI_RAT_ChangedAt] DESC,
   [FI_RAT_PositedAt] DESC
);

GO

-- Rewind function for the annex
-- all rows on or before a point in positing time
CREATE FUNCTION [dbo].[rFI_RAT_FinancialInstrument_Rating_Annex] (
   @positingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_RAT_ID],
   [FI_RAT_PositedAt],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [Metadata_FI_RAT],
   DENSE_RANK() OVER (
      PARTITION BY [FI_RAT_ID], [FI_RAT_Positor]
      ORDER BY [FI_RAT_PositedAt] DESC
   ) AS [FI_RAT_PositingVersion]
FROM
   [dbo].[FI_RAT_FinancialInstrument_Rating_Annex]
WHERE
   [FI_RAT_PositedAt] <= @positingTimepoint;

GO

-- Rewind function for the posit
-- all rows on or before a point in changing time
CREATE FUNCTION [dbo].[rFI_RAT_FinancialInstrument_Rating_Posit] (
   @changingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_RAT_ID],
   [FI_ID],
   [FI_RAT_ChangedAt],
   FI_RAT_FinancialInstrument_Rating
FROM
   [dbo].[FI_RAT_FinancialInstrument_Rating_Posit]
WHERE
   [FI_RAT_ChangedAt] <= @changingTimepoint;

GO

-- Rewind function for the attribute
-- all recorded rows at the given timepoints and
-- having higher than the given reliability
CREATE FUNCTION [dbo].[rFI_RAT_FinancialInstrument_Rating] (
   @changingTimepoint datetime,
   @positingTimepoint datetime,
   @positingVersion int, -- same type as identity
   @reliability tinyint
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_RAT_p].[FI_ID],
   [FI_RAT_p].[FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_p].[FI_RAT_ChangedAt],
   [FI_RAT_a].[FI_RAT_PositedAt],
   [FI_RAT_a].[FI_RAT_Positor],
   [FI_RAT_a].[FI_RAT_Reliability],
   [FI_RAT_p].[FI_RAT_ID],
   [FI_RAT_a].[Metadata_FI_RAT],
   [FI_RAT_a].[FI_RAT_PositingVersion],
   DENSE_RANK() OVER (
      PARTITION BY [FI_ID], [FI_RAT_Positor]
      ORDER BY [FI_RAT_ChangedAt] DESC, [FI_RAT_PositedAt] DESC
   ) AS [FI_RAT_ChangingVersion]
FROM
   [dbo].[rFI_RAT_FinancialInstrument_Rating_Posit](
      @changingTimepoint
   ) [FI_RAT_p]
JOIN
   [dbo].[rFI_RAT_FinancialInstrument_Rating_Annex](
      @positingTimepoint
   ) [FI_RAT_a]
ON
   [FI_RAT_a].[FI_RAT_ID] = [FI_RAT_p].[FI_RAT_ID]
AND
   -- both needed to get the correct rank for ChangingVersion
   [FI_RAT_a].[FI_RAT_Reliability] > @reliability
AND
   [FI_RAT_a].[FI_RAT_PositingVersion] = @positingVersion;

GO

-- function that returns 1 if the previous value is identical
CREATE FUNCTION [dbo].[vFI_RAT_FinancialInstrument_Rating] (
   @posit int,
   @positor tinyint,
   @reliable bit,
   @positedAt datetime
)
RETURNS tinyint AS
BEGIN
   DECLARE @identical int = 0;
   DECLARE @value char(1);
   DECLARE @id int;
   DECLARE @changedAt datetime;
   SELECT
      @value = [FI_RAT_FinancialInstrument_Rating],
      @id = [FI_ID],
      @changedAt = [FI_RAT_ChangedAt]
   FROM
      [dbo].[FI_RAT_FinancialInstrument_Rating_Posit]
   WHERE
      [FI_RAT_ID] = @posit;

   IF(@value IN (
      SELECT
         d.FI_RAT_FinancialInstrument_Rating
      FROM (
         SELECT
            FI_RAT_FinancialInstrument_Rating,
            ROW_NUMBER() OVER (
               PARTITION BY BeforeOrAfter ORDER BY FI_RAT_ChangedAt ASC
            ) AS FollowingVersion,
            ROW_NUMBER() OVER (
               PARTITION BY BeforeOrAfter ORDER BY FI_RAT_ChangedAt DESC
            ) AS PrecedingVersion,
            BeforeOrAfter
         FROM (
            SELECT
               FI_RAT_FinancialInstrument_Rating,
               FI_RAT_ChangedAt,
               CASE
                  WHEN FI_RAT_ChangedAt < @changedAt THEN 'B'
                  ELSE 'A'
               END as BeforeOrAfter
            FROM
               [dbo].[FI_RAT_FinancialInstrument_Rating]
            WHERE
               FI_ID = @id
            AND
               FI_RAT_Positor = @positor
            AND
               FI_RAT_ChangedAt <> @changedAt
            AND
               FI_RAT_Reliable = @reliable
         ) s
      ) d
      WHERE
         (d.BeforeOrAfter = 'A' AND d.FollowingVersion = 1)
      OR
         (d.BeforeOrAfter = 'B' AND d.PrecedingVersion = 1)
   ))
   SET @identical = 1;
   RETURN @identical;
END
GO

-- add the function above as a check that prevents restatements
ALTER TABLE [dbo].[FI_RAT_FinancialInstrument_Rating_Annex]
ADD CONSTRAINT [prFI_RAT_FinancialInstrument_Rating_Annex] CHECK (
[dbo].[vFI_RAT_FinancialInstrument_Rating](
   [FI_RAT_ID],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [FI_RAT_PositedAt]
) = 0);

GO

/*
 * This function is the mother of all time traveling functions.
 *
 * @changingTimepoint
 *     the point in changing time you wish to travel to.
 *
 * @changingVersion
 *     the version with respect to the given point in time
 *     you would like to see, where 1 is the latest version,
 *     2 is the second to latest, and so on.
 *
 * @positingTimepoint
 *     the point in positing time you wish to travel to.
 *
 * @positingVersion
 *     the version with respect to the given point in time
 *     you would like to see, where 1 is the latest version,
 *     2 is the second to latest, and so on.
 *
 * @reliability
 *     the minimum (non-inclusive) reliability of the
 *     returned information.
 *
 */
CREATE FUNCTION [dbo].[pvpvrFI_FinancialInstrument] (
   @changingTimepoint datetime,
   @changingVersion int,
   @positingTimepoint datetime,
   @positingVersion int,
   @reliability tinyint
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI].[FI_ID],
   [FI_RAT].[FI_RAT_FinancialInstrument_Rating],
   [FI_RAT].[FI_RAT_ChangedAt],
   [FI_RAT].[FI_RAT_PositedAt],
   [FI_RAT].[FI_RAT_Positor],
   [FI_RAT].[FI_RAT_Reliability],
   [FI_RAT].[FI_RAT_ID],
   [FI_RAT].[Metadata_FI_RAT]
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[rFI_RAT_FinancialInstrument_Rating](
      @changingTimepoint,
      @positingTimepoint,
      @positingVersion,
      @reliability
   ) [FI_RAT]
ON
   [FI_RAT].[FI_ID] = [FI].[FI_ID]
AND
   [FI_RAT].FI_RAT_ChangingVersion = @changingVersion;

GO

-- the latest view of reliable information
CREATE VIEW [dbo].[llFI_FinancialInstrument]
AS
SELECT
   [FI_ID],
   [FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_ChangedAt],
   [FI_RAT_PositedAt],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [FI_RAT_ID],
   [Metadata_FI_RAT]
FROM
   [dbo].[pvpvrFI_FinancialInstrument] (
      '9999-12-31',
      1,
      '9999-12-31',
      1,
      0
   );

GO

-- the latest view of information with reliability
CREATE FUNCTION [dbo].[llrFI_FinancialInstrument] (
   @reliability tinyint
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_ID],
   [FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_ChangedAt],
   [FI_RAT_PositedAt],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [FI_RAT_ID],
   [Metadata_FI_RAT]
FROM
   [dbo].[pvpvrFI_FinancialInstrument] (
      '9999-12-31',
      1,
      '9999-12-31',
      1,
      @reliability
   );

GO

-- point-in-time for changing time and latest for positing time
CREATE FUNCTION [dbo].[plFI_FinancialInstrument] (
   @changingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_ID],
   [FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_ChangedAt],
   [FI_RAT_PositedAt],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [FI_RAT_ID],
   [Metadata_FI_RAT]
FROM
   [dbo].[pvpvrFI_FinancialInstrument] (
      @changingTimepoint,
      1,
      '9999-12-31',
      1,
      0
   );

GO

-- latest for changing time and point-in-time for positing time
CREATE FUNCTION [dbo].[lpFI_FinancialInstrument] (
   @positingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_ID],
   [FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_ChangedAt],
   [FI_RAT_PositedAt],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [FI_RAT_ID],
   [Metadata_FI_RAT]
FROM
   [dbo].[pvpvrFI_FinancialInstrument] (
      '9999-12-31',
      1,
      @positingTimepoint,
      1,
      0
   );

GO

-- point-in-time for changing time and point-in-time for positing time
CREATE FUNCTION [dbo].[ppFI_FinancialInstrument] (
   @changingTimepoint datetime,
   @positingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
   [FI_ID],
   [FI_RAT_FinancialInstrument_Rating],
   [FI_RAT_ChangedAt],
   [FI_RAT_PositedAt],
   [FI_RAT_Positor],
   [FI_RAT_Reliability],
   [FI_RAT_ID],
   [Metadata_FI_RAT]
FROM
   [dbo].[pvpvrFI_FinancialInstrument] (
      @changingTimepoint,
      1,
      @positingTimepoint,
      1,
      0
   );

GO

-- 0, Don't test anything
-- 1, Simple bitemporal test by Craig Baumunk
-- 2, Our own slightly more complex test
-- 3, Performance test

DECLARE @test int = 2;

/*
 Simple Bitemporal Test (using Anchor Modeling) follow link for data:
 http://bitemporalmodeling.com/bitemporal-support-for-a-temporal-example/
*/

if(@test = 1)
begin

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

-- we should have no information whatsoever yet
select * from FI_RAT_FinancialInstrument_Rating;
-- one row should be returned for annex 1 and 100% reliability
select * from llFI_FinancialInstrument;


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

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(1, '2011-03-17', 1, 1, 42),
(2, '2011-03-17', 1, 1, 42);

-- 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_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'B', '2011-03-16'),
(1, 'A', '2011-03-18');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(1, '2011-03-18', 1, 0, 42),
(3, '2011-03-18', 1, 1, 42),
(4, '2011-03-18', 1, 1, 42);

-- 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_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'C', '2011-03-15'),
(1, '?', '2011-03-20');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(2, '2011-03-19', 1, 0, 42),
(3, '2011-03-19', 1, 0, 42),
(4, '2011-03-19', 1, 0, 42),
(5, '2011-03-19', 1, 1, 42),
(6, '2011-03-19', 1, 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_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'D', '2011-03-17');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(6, '2011-03-20', 1, 0, 42),
(7, '2011-03-20', 1, 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_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'E', '2011-03-18'),
(1, 'D', '2011-03-20');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(8, '2011-03-21', 1, 1, 42),
(9, '2011-03-21', 1, 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');

end


if(@test = 2)
begin

/* Our own simple test

Event 123, System 42 (100%), Arriving at 2001-01-01:
- The rating of the instrument is 'A' from 1995-05-05.
Event 124, System 42 (100%), Arriving at 2002-02-02:
- The rating of the instrument was actually 'B' from 1995-05-05.
Event 125, System 42 (100%), Arriving at 2003-03-03:
- The rating of the instrument has been set to 'F' from 1999-09-09.
Event 126, System 42 (100%), Arriving at 2004-04-04:
- The rating of the instrument was set to 'D' at 1996-06-06.
Event 127, System 42 (100%), Also arriving at 2004-04-04:
- The rating of the instrument was then changed again to 'E' at 1997-07-07.
Event 128, System 42 (100%), Arriving at 2005-05-05:
- The rating was never set to 'F', it was an error.
Event 129, System 99 (100%), Also arriving at 2005-05-05:
- The rating is actually 'F' from '1999-09-09' and onwards.
Event 130, System 99 (50%), Arriving at 2006-06-06:
- The reliability of rating 'F' is downgraded to 50%.
Event 131, System 42 (100%), Arriving at 2004-04-04:
- The rating is 'E' from 1997-08-09 (retroactive restatement wrp to preceding value).
Event 132, System 99 (100%), Arrived at 2001-01-01 but was forgotten until now:
- The rating is 'F' from 1998-09-10 (retroactive restatement wrp to following value).

*/

-- create a financial instrument
insert into FI_FinancialInstrument (Metadata_FI) values (0);

-- Event 123, System 42 (100%), Arriving at 2001-01-01:
-- The rating of the instrument is 'A' from 1995-05-05.
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'A', '1995-05-05');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(1, '2001-01-01', 42, 100, 123);

-- 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('1994-04-04');
select * from plFI_FinancialInstrument('1996-06-06');

-- Event 124, System 42 (100%), Arriving at 2002-02-02:
-- The rating of the instrument was actually 'B' from 1995-05-05.
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'B', '1995-05-05');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
--(1, '2002-02-02', 42, 100, 124), -- is optional!
(2, '2002-02-02', 42, 100, 124);

select dbo.vFI_RAT_FinancialInstrument_Rating(2, 42, 100, '2002-02-02')

-- 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('1994-04-04');
select * from plFI_FinancialInstrument('1996-06-06');
-- before and after in retrospect
select * from ppFI_FinancialInstrument('1994-04-04', '2001-01-01');
select * from ppFI_FinancialInstrument('1996-06-06', '2001-01-01');

-- Event 125, System 42 (100%), Arriving at 2003-03-03:
-- The rating of the instrument has been set to 'F' from 1999-09-09.
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'F', '1999-09-09');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(3, '2003-03-03', 42, 100, 125);

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


-- Event 126, System 42 (100%), Arriving at 2004-04-04:
-- The rating of the instrument was set to 'D' at 1996-06-06.
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'D', '1996-06-06');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(4, '2004-04-04', 42, 100, 126);


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

-- Event 127, System 42 (100%), Also arriving at 2004-04-04:
-- The rating of the instrument was then changed again to 'E' at 1997-07-07.
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'E', '1997-07-07');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(5, '2004-04-04', 42, 100, 127);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before
select * from plFI_FinancialInstrument('1996-06-06');
select * from plFI_FinancialInstrument('1997-07-07');


-- Event 128, System 42 (100%), Arriving at 2005-05-05:
-- The rating was never set to 'F', it was an error.
insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(3, '2005-05-05', 42, 0, 128); -- 0 reliability ~ erased


-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- latest in retrospect
select * from lpFI_FinancialInstrument('2004-04-04');

-- Event 129, System 99 (100%), Also arriving at 2005-05-05:
-- The rating is actually 'F' from '1999-09-09' and onwards.
insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(3, '2005-05-05', 99, 100, 129);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before (notice that system 2 does not have an opinion at this date)
select * from plFI_FinancialInstrument('1998-08-08');
-- latest in retrospect
select * from lpFI_FinancialInstrument('2004-04-04');

-- Event 130, System 99 (50%), Arriving at 2006-06-06:
-- The reliability of rating 'F' is downgraded to 50%.
insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(3, '2006-06-06', 99, 50, 130);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- the latest view with less reliable information excluded
select * from llrFI_FinancialInstrument(50);
-- before (notice that system 99 does not have an opinion at this date)
select * from plFI_FinancialInstrument('1998-08-08');
-- latest in retrospect
select * from lpFI_FinancialInstrument('2004-04-04');
-- latest in a different retrospect (reliability of 'F' is 100% here)
select * from lpFI_FinancialInstrument('2005-05-05');
-- second to latest view (there is no preceding version for system 99)
select * from pvpvrFI_FinancialInstrument('9999-12-31', 2, '9999-12-31', 1, 0);

-- Event 131, System 42 (100%), Arriving at 2004-04-04:
-- The rating is 'E' from 1997-08-09 (restatement).
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'E', '1997-08-09');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(6, '2004-04-04', 42, 100, 131);
-- BANG! Fails restatement check.

-- Event 132, System 99 (100%), Arrived at 2001-01-01 but was forgotten until now:
-- The rating is 'F' from 1998-09-10 (retroactive restatement wrp to following value).
insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
) values
(1, 'F', '1998-09-10');

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
) values
(7, '2001-01-01', 99, 100, 132);
-- BANG! Fails restatement check.

end

if(@test = 3)
begin

------------ PERFORMANCE TESTING (Create a bunch of test data) ---------------
with numGenerator as (
   select
      1 as num
   union all
   select
      num + 1
   from
      numGenerator
   where
      num < 1000000
)
insert into FI_FinancialInstrument (
   Metadata_FI
)
select
   num
from
   numGenerator
option(MAXRECURSION 0);

insert into FI_RAT_FinancialInstrument_Rating_Posit (
   FI_ID,
   FI_RAT_FinancialInstrument_Rating,
   FI_RAT_ChangedAt
)
select
   FI_ID,
   'A',
   getdate()
from
   FI_FinancialInstrument;

insert into FI_RAT_FinancialInstrument_Rating_Annex (
   FI_RAT_ID,
   FI_RAT_PositedAt,
   FI_RAT_Positor,
   FI_RAT_Reliability,
   Metadata_FI_RAT
)
select
   FI_RAT_ID,
   getdate(),
   1,
   1,
   42
from
   FI_RAT_FinancialInstrument_Rating_Posit;

-- look at the execution plan for this
-- tried implementing the two suggested indexes from the plan,
-- but that made the query 60% slower!
declare @start datetime = getdate();
select
   count(*)
from
   llFI_FinancialInstrument
where
   FI_RAT_FinancialInstrument_Rating = 'A';
select DATEDIFF(MS, @start, GETDATE());

end