-
Notifications
You must be signed in to change notification settings - Fork 17
/
ResultsTables.sql
128 lines (110 loc) · 4.02 KB
/
ResultsTables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
--create database RDOSv1;
--use RDOSv1;
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'RDO')
EXEC sp_executesql N'CREATE SCHEMA RDO AUTHORIZATION dbo;';
CREATE TABLE RDO.Settings (
id BIGINT NOT NULL
);
ALTER TABLE RDO.Settings ADD CONSTRAINT PK_Settings PRIMARY KEY(id);
CREATE TABLE RDO.Settings_ModelProfile (
id BIGINT NOT NULL,
analysisType VARCHAR(32) NOT NULL,
softwareVersion VARCHAR(32) NOT NULL,
perilCode VARCHAR(32) NOT NULL,
regionCode VARCHAR(32) NOT NULL,
currencyCode CHAR(3) NOT NULL,
currencyAsOfDate DATE,
modelProfileJson VARCHAR(MAX),
createUser VARCHAR(32),
createDate DATETIME);
ALTER TABLE RDO.Settings_ModelProfile ADD CONSTRAINT PK_Settings_ModelProfile PRIMARY KEY(id);
CREATE TABLE RDO.SettingsDetail (
settingDetailId BIGINT NOT NULL,
settingId BIGINT NOT NULL,
settingName VARCHAR(80) NOT NULL,
settingValue VARCHAR(80) NOT NULL,
settingGroupName VARCHAR(80),
settingGroupValue VARCHAR(80));
ALTER TABLE RDO.SettingsDetail ADD CONSTRAINT PK_SettingsDetail PRIMARY KEY(settingDetailId);
ALTER TABLE RDO.SettingsDetail ADD CONSTRAINT UQ_SettingsDetail UNIQUE (settingId,settingName,settingValue,settingGroupName,settingGroupValue);
CREATE TABLE RDO.Settings_RiskAnalysisProfile (
id BIGINT NOT NULL,
numberOfPeriods BIGINT NOT NULL,
currencyCode CHAR(3) NOT NULL,
currencyAsOfDate DATE,
reportingWindowStartDate DATE,
reportingWindowEndDate DATE,
minimumLossThreshold FLOAT);
ALTER TABLE RDO.Settings_RiskAnalysisProfile ADD CONSTRAINT PK_Settings_RiskAnalysisProfile PRIMARY KEY(id);
CREATE TABLE RDO.Analysis (
id BIGINT NOT NULL,
name VARCHAR(128),
settingsTypeCode VARCHAR(32) NOT NULL,
settingsId BIGINT NOT NULL
);
ALTER TABLE RDO.Analysis ADD CONSTRAINT PK_Analysis PRIMARY KEY(id,settingsId);
CREATE TABLE RDO.AnalysisHierarchy (
analysisId BIGINT NOT NULL,
childAnalysisId BIGINT NOT NULL
);
ALTER TABLE RDO.AnalysisHierarchy ADD CONSTRAINT PK_AnalysisHierarchy PRIMARY KEY(analysisId, childAnalysisId);
CREATE TABLE RDO.AnalysisMap (
analysisId BIGINT NOT NULL,
resultsId BIGINT NOT NULL);
ALTER TABLE RDO.AnalysisMap ADD CONSTRAINT PK_AnalysisMap PRIMARY KEY(analysisId, resultsId);
CREATE TABLE RDO.Result (
id BIGINT NOT NULL);
ALTER TABLE RDO.Result ADD CONSTRAINT PK_Result PRIMARY KEY(id);
CREATE TABLE RDO.ResultMetadata (
id BIGINT NOT NULL,
structureId BIGINT NOT NULL,
positionId BIGINT,
positionName VARCHAR(512) NOT NULL,
settingsId BIGINT NOT NULL,
resultTypeCode VARCHAR(32),
granularityCode VARCHAR(80));
ALTER TABLE RDO.ResultMetadata ADD CONSTRAINT PK_ResultMetadata PRIMARY KEY(id);
CREATE TABLE RDO.Result_ELT (
resultId BIGINT NOT NULL,
facetId BIGINT,
eventId BIGINT NOT NULL,
meanLoss FLOAT NOT NULL,
stdDevC FLOAT NOT NULL,
stdDevI FLOAT NOT NULL,
exposureValue FLOAT NOT NULL);
ALTER TABLE RDO.Result_ELT ADD CONSTRAINT PK_Result_ELT PRIMARY KEY(resultId, eventId);
CREATE TABLE RDO.Result_EP (
resultId BIGINT NOT NULL,
returnPeriod FLOAT NOT NULL,
facetId BIGINT,
aep FLOAT NOT NULL,
oep FLOAT NOT NULL,
tce_aep FLOAT NOT NULL,
tce_oep FLOAT NOT NULL,
eef FLOAT NOT NULL);
ALTER TABLE RDO.Result_EP ADD CONSTRAINT PK_Result_EP PRIMARY KEY(resultId, returnPeriod);
CREATE TABLE RDO.Result_Stats (
resultId BIGINT NOT NULL,
facetId BIGINT,
aal FLOAT NOT NULL,
stdDev FLOAT NOT NULL,
CV FLOAT NOT NULL );
ALTER TABLE RDO.Result_Stats ADD CONSTRAINT PK_Result_Stats PRIMARY KEY(resultId);
CREATE TABLE RDO.Result_PLT (
resultId BIGINT NOT NULL,
periodId BIGINT NOT NULL,
eventId BIGINT NOT NULL,
facetId BIGINT,
loss FLOAT NOT NULL,
lossDate DATE,
eventDate DATE);
ALTER TABLE RDO.Result_PLT ADD CONSTRAINT PK_Result_PLT PRIMARY KEY(resultId, periodId, eventId);
CREATE TABLE RDO.ResultFacetValues (
facetId BIGINT NOT NULL,
admin1GeoId BIGINT,
admin2GeoId BIGINT,
admin3GeoId BIGINT,
postalCodeGeoId BIGINT,
lineOfBusinessCode VARCHAR(32),
cedantCode VARCHAR(80));
ALTER TABLE RDO.ResultFacetValues ADD CONSTRAINT PK_ResultFacetValues PRIMARY KEY(facetId);