· 6 years ago · Jun 04, 2019, 09:44 AM
1DROP TABLE IF EXISTS dbo.FACT_RUN;
2
3DROP TABLE IF EXISTS dbo.DIM_AGE;
4
5DROP TABLE IF EXISTS dbo.DIM_WAVE;
6
7DROP TABLE IF EXISTS dbo.DIM_YEAR;
8
9DROP TABLE IF EXISTS dbo.DIM_TIME;
10
11DROP TABLE IF EXISTS dbo.DIM_FINISHER;
12
13
14-----
15CREATE TABLE [dbo].[DIM_AGE](
16 [Age] [int] NOT NULL PRIMARY KEY,
17 [AgeDivision] [nchar](20) NOT NULL
18);
19
20CREATE TABLE [dbo].[DIM_YEAR](
21 [Edition] [int] NOT NULL PRIMARY KEY,
22 [Year] [int] NOT NULL
23);
24
25CREATE TABLE [dbo].[DIM_FINISHER](
26 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
27 [Names] [nvarchar](50) NOT NULL,
28 [Gender] [nchar](1) NOT NULL,
29 [Country] [nvarchar](50) NOT NULL,
30 [State] [nvarchar](50) NULL,
31 [City] [nvarchar](50) NOT NULL
32);
33
34CREATE TABLE [dbo].[DIM_WAVE](
35 [ID] [int] NOT NULL PRIMARY KEY,
36 [Wave] [nvarchar](50) NOT NULL,
37 [MinBib] [int] NULL,
38 [MaxBib] [int] NULL,
39 [MinTime] [time](7) NOT NULL,
40 [MaxTime] [time](7) NOT NULL
41);
42
43CREATE TABLE [dbo].[DIM_TIME](
44 [ID] [int] NOT NULL PRIMARY KEY,
45 [Hour] [int] NOT NULL,
46 [Minute] [int] NOT NULL,
47 [Second] [int] NOT NULL,
48 [Time] [time](7) NOT NULL
49);
50
51CREATE TABLE [dbo].[FACT_RUN](
52 [Edition] [int] NOT NULL,
53 [Bib] [int] NOT NULL,
54 [FinisherID] [int] NOT NULL,
55 [Age] [int] NOT NULL,
56 [Wave] [int] NOT NULL,
57 [HalfTime] [int] NOT NULL,
58 [Time] [int] NOT NULL,
59 [Pace] [int] NOT NULL,
60 [Rank] [int] NOT NULL,
61 [GenderRank] [int] NOT NULL,
62 [DivisionRank] [int] NOT NULL,
63 [FinishingWave] [int] NOT NULL
64);
65
66
67INSERT INTO DIM_YEAR VALUES(118,2015),(119,2016),(120,2017);
68
69------
70
71
72DROP TABLE IF EXISTS dbo.agehelp;
73
74CREATE TABLE dbo.agehelp(
75 Age int NOT NULL,
76 DivisionName nvarchar(50) NOT NULL
77);
78
79INSERT INTO dbo.agehelp
80SELECT Age, CASE
81 WHEN [Age] <20 THEN '<20'
82 WHEN [Age] >=20 AND [Age] <25 THEN '20-24'
83 WHEN [Age] >=25 AND [Age] <30 THEN '25-29'
84 WHEN [Age] >=30 AND [Age] <35 THEN '30-34'
85 WHEN [Age] >=35 AND [Age] <40 THEN '35-39'
86 WHEN [Age] >=40 AND [Age] <45 THEN '40-44'
87 WHEN [Age] >=45 AND [Age] <50 THEN '45-49'
88 WHEN [Age] >=50 AND [Age] <55 THEN '50-54'
89 WHEN [Age] >=55 AND [Age] <60 THEN '55-59'
90 WHEN [Age] >=60 AND [Age] <65 THEN '60-64'
91 WHEN [Age] >=65 AND [Age] <70 THEN '65-69'
92 WHEN [Age] >=70 AND [Age] <75 THEN '70-74'
93 WHEN [Age] >=75 AND [Age] <80 THEN '75-79'
94 WHEN [Age] >=80 THEN '80+'
95 END AS 'DivisionName'
96FROM dbo.allmarathon_results;
97
98INSERT INTO dbo.DIM_AGE
99SELECT DISTINCT Age, DivisionName
100FROM agehelp;
101
102------
103
104
105INSERT INTO dbo.DIM_WAVE VALUES (1,'Red',0,8000,'00:00:01','03:07:27'),
106 (2,'White',8000,16000,'03:07:27','03:27:17'),
107 (3,'Blue',16000,24000,'03:27:17','03:56:54'),
108 (4,'Yellow',24000,35000,'03:56:54','12:00:00');
109
110
111-------
112
113INSERT INTO dbo.DIM_FINISHER
114SELECT DISTINCT Name, [M/F], Country, State, City FROM dbo.allmarathon_results
115
116-------
117
118DROP TABLE IF EXISTS dbo.timehelp;
119
120CREATE TABLE [dbo].[timehelp]
121(
122 [ID] [int] NOT NULL,
123 [Hour] [int] NOT NULL,
124 [Minute] [int] NOT NULL,
125 [Second] [int] NOT NULL,
126 [Time] [time](7) NOT NULL
127);
128
129INSERT INTO dbo.timehelp
130SELECT DISTINCT Datepart(hour,Half)*3600+Datepart(minute,Half)*60+Datepart(second,Half) AS id,
131Datepart(hour,Half) as H, Datepart(minute,Half) as M, Datepart(second,Half) as S, Half D
132FROM dbo.allmarathon_results;
133
134INSERT INTO dbo.timehelp
135SELECT DISTINCT Datepart(hour,[Official Time])*3600+Datepart(minute,[Official Time])*60+Datepart(second,[Official Time]) AS id,
136 Datepart(hour,[Official Time]) as H, Datepart(minute,[Official Time]) as M, Datepart(second,[Official Time]) as S, [Official Time] as D
137FROM dbo.allmarathon_results;
138
139INSERT INTO dbo.timehelp
140SELECT DISTINCT Datepart(hour,Pace)*3600+Datepart(minute,Pace)*60+Datepart(second,Pace) AS id,
141 Datepart(hour,Pace) as H, Datepart(minute,Pace) as M, Datepart(second,Pace) as S, Pace as D
142FROM dbo.allmarathon_results;
143
144INSERT INTO dbo.DIM_TIME
145SELECT DISTINCT * FROM dbo.timehelp;
146
147-------
148
149INSERT INTO dbo.FACT_RUN
150SELECT Y.Edition, H.Bib, F.ID, A.Age, H.Wave,
151 Datepart(hour,H.Half)*3600+Datepart(minute,H.Half)*60+Datepart(second,H.Half) AS htime,
152 Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) AS otime,
153 Datepart(hour,H.Pace)*3600+Datepart(minute,H.Pace)*60+Datepart(second,H.Pace) AS p,
154 H.Overall, H.Gender, H.Division, W.ID
155FROM dbo.allmarathon_results AS H
156 JOIN dbo.DIM_FINISHER F ON (Concat(H.Name,H.Country,H.State,H.City) LIKE Concat(F.Names,F.Country,F.State,F.City))
157 JOIN dbo.DIM_AGE A ON(A.Age = H.Age)
158 JOIN dbo.DIM_YEAR Y ON (Y.Year = H.Year)
159 JOIN dbo.DIM_WAVE W ON ([Official Time] < W.MaxTime
160 AND [Official Time] >= W.MinTime);
161
162--------
163ALTER TABLE dbo.FACT_RUN ADD
164CONSTRAINT fk_F FOREIGN KEY(FinisherID) REFERENCES dbo.DIM_FINISHER(ID);
165
166ALTER TABLE dbo.FACT_RUN ADD
167CONSTRAINT fk_A FOREIGN KEY(Age) REFERENCES dbo.DIM_AGE(Age);
168
169ALTER TABLE dbo.FACT_RUN ADD
170CONSTRAINT fk_E FOREIGN KEY(Edition) REFERENCES dbo.DIM_YEAR(Edition);
171
172ALTER TABLE dbo.FACT_RUN ADD
173CONSTRAINT fk_W FOREIGN KEY(Wave) REFERENCES dbo.DIM_WAVE(ID);
174
175ALTER TABLE dbo.FACT_RUN ADD
176CONSTRAINT fk_FW FOREIGN KEY(FinishingWave) REFERENCES dbo.DIM_WAVE(ID);
177
178ALTER TABLE dbo.FACT_RUN ADD
179CONSTRAINT fk_OT FOREIGN KEY(Time) REFERENCES dbo.DIM_TIME(ID);
180
181ALTER TABLE dbo.FACT_RUN ADD
182CONSTRAINT fk_HT FOREIGN KEY(HalfTime) REFERENCES dbo.DIM_TIME(ID);
183
184ALTER TABLE dbo.FACT_RUN ADD
185CONSTRAINT fk_PT FOREIGN KEY(Pace) REFERENCES dbo.DIM_TIME(ID);
186
187UPDATE dbo.DIM_FINISHER
188SET State = 'unspecified'
189WHERE State IS NULL;
190
191
192
193----pomocnicze-----
194INSERT INTO dbo.DIM_AGE values (18,'<20'),(19,'<20'),(20,'20-24'),(21,'20-24'),(22,'20-24'),(23,'20-24'),(24,'20-24'),
195 (25,'25-29'),(26,'25-29'),(27,'25-29'),(28,'25-29'),(29,'25-29'),
196 (30,'30-34'),(31,'30-34'),(32,'30-34'),(33,'30-34'),(34,'30-34'),
197 (35,'35-39'),(36,'35-39'),(37,'35-39'),(38,'35-39'),(39,'35-39'),
198 (40,'40-44'),(41,'40-44'),(42,'40-44'),(43,'40-44'),(44,'40-44'),
199 (45,'45-49'),(46,'45-49'),(47,'45-49'),(48,'45-49'),(49,'45-49'),
200 (50,'50-54'),(51,'50-54'),(52,'50-54'),(53,'50-54'),(54,'50-54'),
201 (55,'55-59'),(56,'55-59'),(57,'55-59'),(58,'55-59'),(59,'55-59'),
202 (60,'60-64'),(61,'60-64'),(62,'60-64'),(63,'60-64'),(64,'60-64'),
203 (65,'65-69'),(66,'65-69'),(67,'65-69'),(68,'65-69'),(69,'65-69'),
204 (70,'70-74'),(71,'70-74'),(72,'70-74'),(73,'70-74'),(74,'70-74'),
205 (75,'75-79'),(76,'75-79'),(77,'75-79'),(78,'75-79'),(79,'75-79'),
206 (80,'80+'),(81,'80+'),(82,'80+'),(83,'80+'),(84,'80+'),(85,'80+'),(86,'80+'),(87,'80+'),(89,'80+'),(90,'80+'),(91,'80+'),(92,'80+');
207
208----------------
209
210
211ALTER TABLE dbo.allmarathon_results ADD Wave AS(
212 CASE
213 WHEN Bib < 8000 THEN 1
214 WHEN Bib >= 8000 AND Bib < 16000 THEN 2
215 WHEN Bib >= 16000 AND Bib < 24000 THEN 3
216 WHEN Bib >= 2400 THEN 4
217 END );
218
219INSERT INTO dbo.DIM_BIB
220SELECT DISTINCT Bib, CASE
221 WHEN Bib < 8000 THEN 'Red'
222 WHEN Bib >= 8000 AND Bib < 16000 THEN 'White'
223 WHEN Bib >= 16000 AND Bib < 24000 THEN 'Blue'
224 WHEN Bib >= 2400 THEN 'Yellow'
225 END AS wave,
226 CASE
227 WHEN Bib < 8000 THEN 1
228 WHEN Bib >= 8000 AND Bib < 16000 THEN 11247
229 WHEN Bib >= 16000 AND Bib < 24000 THEN 12447
230 WHEN Bib >= 2400 THEN 14214
231 END AS mint,
232 CASE
233 WHEN Bib < 8000 THEN 11247
234 WHEN Bib >= 8000 AND Bib < 16000 THEN 12447
235 WHEN Bib >= 16000 AND Bib < 24000 THEN 14214
236 WHEN Bib >= 2400 THEN 19200
237 END AS maxt
238FROM dbo.allmarathon_results;
239
240
241INSERT INTO dbo.FACT_RUN
242SELECT Y.Edition, H.Bib, F.ID, A.Age, H.Wave,
243 Datepart(hour,H.Half)*3600+Datepart(minute,H.Half)*60+Datepart(second,H.Half) AS htime,
244 Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) AS otime,
245 Datepart(hour,H.Pace)*3600+Datepart(minute,H.Pace)*60+Datepart(second,H.Pace) AS p,
246 H.Overall, H.Gender, H.Division,
247 CASE
248 WHEN Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) < 11247 THEN 1
249 WHEN Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) >= 11247 AND Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) < 12447 THEN 2
250 WHEN Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) >= 12447 AND Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) < 14214 THEN 3
251 WHEN Datepart(hour,H.[Official Time])*3600+Datepart(minute,H.[Official Time])*60+Datepart(second,H.[Official Time]) >= 14214 THEN 4
252 END AS fwave
253FROM dbo.allmarathon_results AS H
254 JOIN dbo.DIM_FINISHER F ON (Concat(H.Name,H.Country,H.State,H.City) LIKE Concat(F.Names,F.Country,F.State,F.City))
255 JOIN dbo.DIM_AGE A ON(A.Age = H.Age)
256 JOIN dbo.DIM_YEAR Y ON (Y.Year = H.Year)