anchormodeling

Bitemporal Anchor Modeling 1C2R Partitioning

Nov 18th, 2011
269
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  Simple Bitemporal Test (using Anchor Modeling)
  3.  http://www.temporaldata.com/wp-content/uploads/2011/04/bitemporal-support-for-a-temporal-example.swf
  4. */
  5.  
  6. -- Partition function for two partitions, 0 and 1
  7. CREATE PARTITION FUNCTION ReliabilityPartition (bit)
  8. AS RANGE LEFT FOR VALUES(0, 1);
  9.  
  10. GO
  11.  
  12. -- Partition scheme using the function above
  13. CREATE PARTITION SCHEME ReliabilityScheme
  14. AS PARTITION ReliabilityPartition
  15. ALL TO ([PRIMARY]);
  16.  
  17. GO
  18.  
  19. -- Anchor table
  20. CREATE TABLE [dbo].[FI_FinancialInstrument] (
  21.     [FI_ID] [int] IDENTITY(1,1) NOT NULL,
  22.     [Metadata_FI] [int] NOT NULL,
  23.     PRIMARY KEY CLUSTERED (
  24.         [FI_ID] ASC
  25.     )
  26. );
  27.  
  28. GO
  29.  
  30. -- Attribute table
  31. CREATE TABLE [dbo].[FI_RAT_FinancialInstrument_Rating] (
  32.     [FI_ID] [int] NOT NULL,
  33.     [FI_RAT_FinancialInstrument_Rating] [char](1) NOT NULL,
  34.     -- Changing time (Anchor Modeling), Valid time (Snodgrass), Effective time (Johnston)
  35.     [FI_RAT_ChangedAt] [date] NOT NULL,
  36.     -- Recording time (Anchor Modeling), Transaction time (Snodgrass), Assertion time (Johnston)
  37.     [FI_RAT_RecordedAt] [date] NOT NULL,
  38.     [FI_RAT_ErasedAt] [date] NULL,
  39.     [FI_RAT_Erased] [bit] NOT NULL DEFAULT 0,
  40.     [Metadata_FI_RAT] [int] NOT NULL,
  41.     PRIMARY KEY CLUSTERED (
  42.         [FI_ID] ASC,
  43.         [FI_RAT_ChangedAt] DESC,
  44.         [FI_RAT_RecordedAt] DESC,
  45.         [FI_RAT_Erased] ASC
  46.     ),
  47.     FOREIGN KEY ([FI_ID]) REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
  48. ) ON ReliabilityScheme([FI_RAT_Erased]);
  49.  
  50. GO
  51.  
  52. -- Time-traveling function of the attribute
  53. CREATE FUNCTION [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
  54.     @changingTimepoint datetime,
  55.     @recordingTimepoint datetime   
  56. )
  57. RETURNS TABLE
  58. RETURN
  59. SELECT
  60.     [FI_ID],
  61.     [FI_RAT_FinancialInstrument_Rating],
  62.     [FI_RAT_ChangedAt],
  63.     [FI_RAT_RecordedAt],
  64.     [FI_RAT_ErasedAt],
  65.     [FI_RAT_Erased],
  66.     [Metadata_FI_RAT]
  67. FROM
  68.     [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
  69. WHERE (
  70.         -- use the whole recorded partition
  71.         [FI_RAT].[FI_RAT_Erased] = 0
  72.     OR (
  73.             -- and in the erased partition
  74.             [FI_RAT].[FI_RAT_Erased] = 1
  75.         AND
  76.             -- search for what was valid at the time
  77.             [FI_RAT].FI_RAT_ErasedAt > @recordingTimepoint  
  78.     )
  79. )
  80. AND
  81.    [FI_RAT].FI_RAT_ChangedAt <= @changingTimepoint
  82. AND
  83.    [FI_RAT].FI_RAT_RecordedAt <= @recordingTimepoint;
  84.  
  85. GO
  86.  
  87. -- Insert trigger on the table
  88. CREATE TRIGGER [dbo].[itFI_RAT_FinancialInstrument_Rating] ON [dbo].[FI_RAT_FinancialInstrument_Rating]
  89. INSTEAD OF INSERT
  90. AS
  91. BEGIN
  92. SET NOCOUNT ON;
  93. UPDATE [FI_RAT]
  94. SET
  95.     FI_RAT_ErasedAt = ISNULL(i.[FI_RAT_ErasedAt], getdate()), -- DW/OLTP
  96.     FI_RAT_Erased = 1
  97. FROM
  98.     [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
  99. JOIN
  100.     inserted i
  101. ON
  102.     [FI_RAT].FI_ID = i.FI_ID
  103. AND
  104.     [FI_RAT].FI_RAT_ChangedAt = i.FI_RAT_ChangedAt
  105. WHERE
  106.     i.[FI_RAT_Erased] = 1
  107. AND
  108.     [FI_RAT].FI_RAT_Erased = 0;
  109.  
  110. INSERT INTO [dbo].[FI_RAT_FinancialInstrument_Rating] (
  111.     [FI_ID],
  112.     [FI_RAT_FinancialInstrument_Rating],
  113.     [FI_RAT_ChangedAt],
  114.     [FI_RAT_RecordedAt],
  115.     [FI_RAT_ErasedAt],
  116.     [FI_RAT_Erased],
  117.     [Metadata_FI_RAT]
  118. )
  119. SELECT
  120.     i.[FI_ID],
  121.     i.[FI_RAT_FinancialInstrument_Rating],
  122.     ISNULL(i.[FI_RAT_ChangedAt], getdate()), -- DW/OLTP
  123.     ISNULL(i.[FI_RAT_RecordedAt], getdate()), -- DW/OLTP
  124.     null,
  125.     ISNULL(i.[FI_RAT_Erased], 0),
  126.     i.[Metadata_FI_RAT]
  127. FROM
  128.     inserted i
  129. WHERE
  130.     i.FI_RAT_Erased is null
  131. OR
  132.     i.FI_RAT_Erased = 0;
  133. END
  134.  
  135. GO
  136.  
  137.  
  138. /*
  139.     There are nine 'perspectives' of the information resulting
  140.     from latest, point-in-time, and interval perspectives
  141.     taken over the two timelines (changing and recording).
  142.     I will show three of them here.
  143. */
  144.  
  145. -- latest for changing time and latest for recording time
  146. CREATE VIEW [dbo].[llFI_FinancialInstrument]
  147. AS
  148. SELECT
  149.    [FI].FI_ID,
  150.    [FI].Metadata_FI,
  151.    [FI_RAT].FI_RAT_FinancialInstrument_Rating,
  152.    [FI_RAT].FI_RAT_ChangedAt,
  153.    [FI_RAT].FI_RAT_RecordedAt,
  154.    [FI_RAT].FI_RAT_Erased,
  155.    [FI_RAT].Metadata_FI_RAT
  156. FROM
  157.    [dbo].[FI_FinancialInstrument] [FI]
  158. LEFT JOIN
  159.    [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
  160. ON
  161.    [FI_RAT].FI_RAT_Erased = 0 -- select partition
  162. AND
  163.    [FI_RAT].FI_ID = [FI].FI_ID
  164. AND
  165.    [FI_RAT].FI_RAT_ChangedAt = (
  166.       SELECT
  167.          max(sub.FI_RAT_ChangedAt)
  168.       FROM
  169.          [dbo].[FI_RAT_FinancialInstrument_Rating] sub
  170.       WHERE
  171.          sub.FI_RAT_Erased = 0
  172.       AND
  173.          sub.FI_ID = [FI].FI_ID
  174.     );
  175.  
  176. GO
  177.  
  178. -- point-in-time for changing time and latest for recording time
  179. CREATE FUNCTION [dbo].[plFI_FinancialInstrument] (
  180.     @timepoint datetime
  181. )
  182. RETURNS TABLE
  183. RETURN
  184. SELECT
  185.    [FI].FI_ID,
  186.    [FI].Metadata_FI,
  187.    [FI_RAT].FI_RAT_FinancialInstrument_Rating,
  188.    [FI_RAT].FI_RAT_ChangedAt,
  189.    [FI_RAT].FI_RAT_RecordedAt,
  190.    [FI_RAT].FI_RAT_Erased,
  191.    [FI_RAT].Metadata_FI_RAT
  192. FROM
  193.    [dbo].[FI_FinancialInstrument] [FI]
  194. LEFT JOIN
  195.    [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
  196. ON
  197.    [FI_RAT].FI_RAT_Erased = 0 -- select partition
  198. AND
  199.    [FI_RAT].FI_ID = [FI].FI_ID
  200. AND
  201.    [FI_RAT].FI_RAT_ChangedAt = (
  202.       SELECT
  203.          max(sub.FI_RAT_ChangedAt)
  204.       FROM
  205.          [dbo].[FI_RAT_FinancialInstrument_Rating] sub
  206.       WHERE
  207.          sub.FI_RAT_Erased = 0
  208.       AND
  209.          sub.FI_ID = [FI].FI_ID
  210.       AND
  211.          sub.FI_RAT_ChangedAt <= @timepoint
  212.     );
  213.  
  214. GO
  215.  
  216. -- point-in-time for changing time and point-in-time for recording time
  217. CREATE FUNCTION [dbo].[ppFI_FinancialInstrument] (
  218.     @changingTimepoint datetime,
  219.     @recordingTimepoint datetime   
  220. )
  221. RETURNS TABLE
  222. RETURN
  223. SELECT
  224.    [FI].FI_ID,
  225.    [FI].Metadata_FI,
  226.    [FI_RAT].FI_RAT_FinancialInstrument_Rating,
  227.    [FI_RAT].FI_RAT_ChangedAt,
  228.    [FI_RAT].FI_RAT_RecordedAt,
  229.    [FI_RAT].FI_RAT_ErasedAt,
  230.    [FI_RAT].FI_RAT_Erased,
  231.    [FI_RAT].Metadata_FI_RAT
  232. FROM
  233.    [dbo].[FI_FinancialInstrument] [FI]
  234. LEFT JOIN
  235.    [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
  236.       @changingTimepoint,
  237.       @recordingTimepoint
  238.    ) [FI_RAT]
  239. ON
  240.    [FI_RAT].FI_ID = [FI].FI_ID
  241. AND
  242.    [FI_RAT].FI_RAT_ChangedAt = (
  243.       SELECT
  244.          max(sub.FI_RAT_ChangedAt)
  245.       FROM
  246.          [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
  247.             @changingTimepoint,
  248.             @recordingTimepoint
  249.          ) sub
  250.       WHERE
  251.          sub.FI_ID = [FI_RAT].FI_ID
  252.     );
  253.  
  254. GO
  255.  
  256. -- create one financial instrument
  257. delete from FI_FinancialInstrument;
  258. insert into FI_FinancialInstrument (Metadata_FI) values (42);
  259.  
  260. -- we should have no information whatsoever yet
  261. delete from FI_RAT_FinancialInstrument_Rating;
  262. select * from FI_RAT_FinancialInstrument_Rating;
  263. select * from llFI_FinancialInstrument;
  264.  
  265. -- DAY 1
  266. -- insert the first ratings for the created financial instrument
  267. insert into FI_RAT_FinancialInstrument_Rating (
  268.     [FI_ID],
  269.     [FI_RAT_FinancialInstrument_Rating],
  270.     [FI_RAT_ChangedAt],
  271.     [FI_RAT_RecordedAt],
  272.     [Metadata_FI_RAT]
  273. ) values (1, 'A', '2011-03-17', '2011-03-17', 42);
  274. insert into FI_RAT_FinancialInstrument_Rating (
  275.     [FI_ID],
  276.     [FI_RAT_FinancialInstrument_Rating],
  277.     [FI_RAT_ChangedAt],
  278.     [FI_RAT_RecordedAt],
  279.     [Metadata_FI_RAT]
  280. ) values (1, '?', '2011-03-19', '2011-03-17', 42);
  281.  
  282. -- all rows in the attribute table
  283. select * from FI_RAT_FinancialInstrument_Rating;
  284. -- the latest view
  285. select * from llFI_FinancialInstrument;
  286. -- every day of the week in the test along changing time
  287. select * from plFI_FinancialInstrument('2011-03-15');
  288. select * from plFI_FinancialInstrument('2011-03-16');
  289. select * from plFI_FinancialInstrument('2011-03-17');
  290. select * from plFI_FinancialInstrument('2011-03-18');
  291. select * from plFI_FinancialInstrument('2011-03-19');
  292. select * from plFI_FinancialInstrument('2011-03-20');
  293.  
  294. -- DAY 2
  295. -- Note that the following requires user knowledge of existing data
  296. -- We need to remove what was wrong before we can insert the correction
  297. insert into FI_RAT_FinancialInstrument_Rating (
  298.     [FI_ID],
  299.     [FI_RAT_ChangedAt],
  300.     [FI_RAT_RecordedAt],
  301.     [FI_RAT_ErasedAt],
  302.     [FI_RAT_Erased],
  303.     [Metadata_FI_RAT]
  304. ) values (1, '2011-03-17', '2011-03-17', '2011-03-18', 1, 43);
  305.    
  306. insert into FI_RAT_FinancialInstrument_Rating (
  307.     [FI_ID],
  308.     [FI_RAT_FinancialInstrument_Rating],
  309.     [FI_RAT_ChangedAt],
  310.     [FI_RAT_RecordedAt],
  311.     [Metadata_FI_RAT]
  312. ) values (1, 'B', '2011-03-16', '2011-03-18', 43);
  313. insert into FI_RAT_FinancialInstrument_Rating (
  314.     [FI_ID],
  315.     [FI_RAT_FinancialInstrument_Rating],
  316.     [FI_RAT_ChangedAt],
  317.     [FI_RAT_RecordedAt],
  318.     [Metadata_FI_RAT]
  319. ) values (1, 'A', '2011-03-18', '2011-03-18', 43);
  320.  
  321. -- all rows in the attribute table
  322. select * from FI_RAT_FinancialInstrument_Rating;
  323. -- the latest view
  324. select * from llFI_FinancialInstrument;
  325. -- every day of the week in the test along changing time
  326. select * from plFI_FinancialInstrument('2011-03-15');
  327. select * from plFI_FinancialInstrument('2011-03-16');
  328. select * from plFI_FinancialInstrument('2011-03-17');
  329. select * from plFI_FinancialInstrument('2011-03-18');
  330. select * from plFI_FinancialInstrument('2011-03-19');
  331. select * from plFI_FinancialInstrument('2011-03-20');
  332. -- some interesting intersections
  333. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
  334. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
  335.  
  336. -- DAY 3
  337. insert into FI_RAT_FinancialInstrument_Rating (
  338.     [FI_ID],
  339.     [FI_RAT_ChangedAt],
  340.     [FI_RAT_RecordedAt],
  341.     [FI_RAT_ErasedAt],
  342.     [FI_RAT_Erased],
  343.     [Metadata_FI_RAT]
  344. ) values (1, '2011-03-16', '2011-03-18', '2011-03-19', 1, 44);
  345. insert into FI_RAT_FinancialInstrument_Rating (
  346.     [FI_ID],
  347.     [FI_RAT_ChangedAt],
  348.     [FI_RAT_RecordedAt],
  349.     [FI_RAT_ErasedAt],
  350.     [FI_RAT_Erased],
  351.     [Metadata_FI_RAT]
  352. ) values (1, '2011-03-18', '2011-03-18', '2011-03-19', 1, 44);
  353. insert into FI_RAT_FinancialInstrument_Rating (
  354.     [FI_ID],
  355.     [FI_RAT_ChangedAt],
  356.     [FI_RAT_RecordedAt],
  357.     [FI_RAT_ErasedAt],
  358.     [FI_RAT_Erased],
  359.     [Metadata_FI_RAT]
  360. ) values (1, '2011-03-19', '2011-03-17', '2011-03-19', 1, 44);
  361. insert into FI_RAT_FinancialInstrument_Rating (
  362.     [FI_ID],
  363.     [FI_RAT_FinancialInstrument_Rating],
  364.     [FI_RAT_ChangedAt],
  365.     [FI_RAT_RecordedAt],
  366.     [Metadata_FI_RAT]
  367. ) values (1, 'C', '2011-03-15', '2011-03-19', 44);
  368. insert into FI_RAT_FinancialInstrument_Rating (
  369.     [FI_ID],
  370.     [FI_RAT_FinancialInstrument_Rating],
  371.     [FI_RAT_ChangedAt],
  372.     [FI_RAT_RecordedAt],
  373.     [Metadata_FI_RAT]
  374. ) values (1, '?', '2011-03-20', '2011-03-19', 44);
  375.  
  376. -- all rows in the attribute table
  377. select * from FI_RAT_FinancialInstrument_Rating;
  378. -- the latest view
  379. select * from llFI_FinancialInstrument;
  380. -- every day of the week in the test along changing time
  381. select * from plFI_FinancialInstrument('2011-03-15');
  382. select * from plFI_FinancialInstrument('2011-03-16');
  383. select * from plFI_FinancialInstrument('2011-03-17');
  384. select * from plFI_FinancialInstrument('2011-03-18');
  385. select * from plFI_FinancialInstrument('2011-03-19');
  386. select * from plFI_FinancialInstrument('2011-03-20');
  387. -- some interesting intersections
  388. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
  389. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
  390. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');
  391.  
  392. -- DAY 4
  393. insert into FI_RAT_FinancialInstrument_Rating (
  394.     [FI_ID],
  395.     [FI_RAT_ChangedAt],
  396.     [FI_RAT_RecordedAt],
  397.     [FI_RAT_ErasedAt],
  398.     [FI_RAT_Erased],
  399.     [Metadata_FI_RAT]
  400. ) values (1, '2011-03-20', '2011-03-19', '2011-03-20', 1, 45);
  401. insert into FI_RAT_FinancialInstrument_Rating (
  402.     [FI_ID],
  403.     [FI_RAT_FinancialInstrument_Rating],
  404.     [FI_RAT_ChangedAt],
  405.     [FI_RAT_RecordedAt],
  406.     [Metadata_FI_RAT]
  407. ) values (1, 'D', '2011-03-17', '2011-03-20', 45);
  408.  
  409. -- all rows in the attribute table
  410. select * from FI_RAT_FinancialInstrument_Rating;
  411. -- the latest view
  412. select * from llFI_FinancialInstrument;
  413. -- every day of the week in the test along changing time
  414. select * from plFI_FinancialInstrument('2011-03-15');
  415. select * from plFI_FinancialInstrument('2011-03-16');
  416. select * from plFI_FinancialInstrument('2011-03-17');
  417. select * from plFI_FinancialInstrument('2011-03-18');
  418. select * from plFI_FinancialInstrument('2011-03-19');
  419. select * from plFI_FinancialInstrument('2011-03-20');
  420. -- some interesting intersections
  421. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
  422. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
  423. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');
  424. select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-20');
  425.  
  426.  
  427. -- DAY 5
  428. insert into FI_RAT_FinancialInstrument_Rating (
  429.     [FI_ID],
  430.     [FI_RAT_FinancialInstrument_Rating],
  431.     [FI_RAT_ChangedAt],
  432.     [FI_RAT_RecordedAt],
  433.     [Metadata_FI_RAT]
  434. ) values (1, 'E', '2011-03-18', '2011-03-21', 46);
  435. insert into FI_RAT_FinancialInstrument_Rating (
  436.     [FI_ID],
  437.     [FI_RAT_FinancialInstrument_Rating],
  438.     [FI_RAT_ChangedAt],
  439.     [FI_RAT_RecordedAt],
  440.     [Metadata_FI_RAT]
  441. ) values (1, 'D', '2011-03-20', '2011-03-21', 46);
  442.  
  443. -- all rows in the attribute table
  444. select * from FI_RAT_FinancialInstrument_Rating;
  445. -- the latest view
  446. select * from llFI_FinancialInstrument;
  447. -- every day of the week in the test along changing time
  448. select * from plFI_FinancialInstrument('2011-03-15');
  449. select * from plFI_FinancialInstrument('2011-03-16');
  450. select * from plFI_FinancialInstrument('2011-03-17');
  451. select * from plFI_FinancialInstrument('2011-03-18');
  452. select * from plFI_FinancialInstrument('2011-03-19');
  453. select * from plFI_FinancialInstrument('2011-03-20');
  454. -- some interesting intersections
  455. select * from ppFI_FinancialInstrument('2011-03-18', '2011-03-20');
  456. select * from ppFI_FinancialInstrument('2011-03-18', '2011-03-21');
  457. select * from ppFI_FinancialInstrument('2011-03-19', '2011-03-20');
  458. select * from ppFI_FinancialInstrument('2011-03-19', '2011-03-21');
  459.  
  460. /* Our own simple test
  461.  
  462. Event 1, Arriving at 2005-09-10:
  463. - The rating of the instrument is 'A' from 2001-01-01.
  464. Event 2, Arriving at 2005-10-12:
  465. - The rating of the instrument was actually 'B' from 2001-01-01.
  466. Event 3, Arriving at 2005-10-20:
  467. - The rating of the instrument has been set to 'F' from 2003-02-13.
  468. Event 4, Arriving at 2005-11-09:
  469. - The rating of the instrument was set to 'D' at 2002-04-10.
  470. Event 5, Also arriving at 2005-11-09:
  471. - The rating of the instrument was then changed again to 'E' at 2002-08-20.
  472. Event 6, Arriving at 2006-09-21:
  473. - The rating was never set to 'F', it was an error.
  474.  
  475. */
  476.  
  477. -- create another financial instrument
  478. insert into FI_FinancialInstrument (Metadata_FI) values (42);
  479.  
  480. -- EVENT 1
  481. insert into FI_RAT_FinancialInstrument_Rating (
  482.     [FI_ID],
  483.     [FI_RAT_FinancialInstrument_Rating],
  484.     [FI_RAT_ChangedAt],
  485.     [FI_RAT_RecordedAt],
  486.     [Metadata_FI_RAT]
  487. ) values (2, 'A', '2001-01-01', '2005-09-10', 555);
  488.  
  489. -- all rows in the attribute table
  490. select * from FI_RAT_FinancialInstrument_Rating;
  491. -- the latest view
  492. select * from llFI_FinancialInstrument;
  493. -- before and after
  494. select * from plFI_FinancialInstrument('2000-12-31');
  495. select * from plFI_FinancialInstrument('2001-01-02');
  496.  
  497.  
  498. -- EVENT 2
  499. insert into FI_RAT_FinancialInstrument_Rating (
  500.     [FI_ID],
  501.     [FI_RAT_ChangedAt],
  502.     [FI_RAT_ErasedAt],
  503.     [FI_RAT_Erased],
  504.     [Metadata_FI_RAT]
  505. ) values (2, '2001-01-01', '2005-10-12', 1, 556);
  506. insert into FI_RAT_FinancialInstrument_Rating (
  507.     [FI_ID],
  508.     [FI_RAT_FinancialInstrument_Rating],
  509.     [FI_RAT_ChangedAt],
  510.     [FI_RAT_RecordedAt],
  511.     [Metadata_FI_RAT]
  512. ) values (2, 'B', '2001-01-01', '2005-10-12', 556);
  513.  
  514. -- all rows in the attribute table
  515. select * from FI_RAT_FinancialInstrument_Rating;
  516. -- the latest view
  517. select * from llFI_FinancialInstrument;
  518. -- before and after
  519. select * from plFI_FinancialInstrument('2000-12-31');
  520. select * from plFI_FinancialInstrument('2001-01-02');
  521. -- before and after in retrospect
  522. select * from ppFI_FinancialInstrument('2000-12-31', '2005-09-22');
  523. select * from ppFI_FinancialInstrument('2001-01-02', '2005-09-22');
  524.  
  525.  
  526. -- EVENT 3
  527. insert into FI_RAT_FinancialInstrument_Rating (
  528.     [FI_ID],
  529.     [FI_RAT_FinancialInstrument_Rating],
  530.     [FI_RAT_ChangedAt],
  531.     [FI_RAT_RecordedAt],
  532.     [Metadata_FI_RAT]
  533. ) values (2, 'F', '2003-02-13', '2005-10-20', 557);
  534.  
  535. -- all rows in the attribute table
  536. select * from FI_RAT_FinancialInstrument_Rating;
  537. -- the latest view
  538. select * from llFI_FinancialInstrument;
  539.  
  540.  
  541. -- EVENT 4
  542. insert into FI_RAT_FinancialInstrument_Rating (
  543.     [FI_ID],
  544.     [FI_RAT_FinancialInstrument_Rating],
  545.     [FI_RAT_ChangedAt],
  546.     [FI_RAT_RecordedAt],
  547.     [Metadata_FI_RAT]
  548. ) values (2, 'D', '2002-04-10', '2005-11-09', 558);
  549.  
  550. -- all rows in the attribute table
  551. select * from FI_RAT_FinancialInstrument_Rating;
  552. -- the latest view
  553. select * from llFI_FinancialInstrument;
  554. -- before
  555. select * from plFI_FinancialInstrument('2003-02-12');
  556.  
  557.  
  558. -- EVENT 5
  559. insert into FI_RAT_FinancialInstrument_Rating (
  560.     [FI_ID],
  561.     [FI_RAT_FinancialInstrument_Rating],
  562.     [FI_RAT_ChangedAt],
  563.     [FI_RAT_RecordedAt],
  564.     [Metadata_FI_RAT]
  565. ) values (2, 'E', '2002-08-20', '2005-11-09', 559);
  566.  
  567. -- all rows in the attribute table
  568. select * from FI_RAT_FinancialInstrument_Rating;
  569. -- the latest view
  570. select * from llFI_FinancialInstrument;
  571. -- before
  572. select * from plFI_FinancialInstrument('2003-02-12');
  573.  
  574.  
  575. -- EVENT 6
  576. insert into FI_RAT_FinancialInstrument_Rating (
  577.     [FI_ID],
  578.     [FI_RAT_ChangedAt],
  579.     [FI_RAT_ErasedAt],
  580.     [FI_RAT_Erased],
  581.     [Metadata_FI_RAT]
  582. ) values (2, '2003-02-13', '2006-09-21', 1, 557);
  583.  
  584. -- all rows in the attribute table
  585. select * from FI_RAT_FinancialInstrument_Rating;
  586. -- the latest view
  587. select * from llFI_FinancialInstrument;
  588. -- before
  589. select * from plFI_FinancialInstrument('2003-02-12');
  590. -- latest in retrospect
  591. select * from ppFI_FinancialInstrument(GETDATE(), '2006-09-20');
  592. select * from ppFI_FinancialInstrument(GETDATE(), '2005-10-12');
RAW Paste Data