/*
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