· 6 years ago · Jun 13, 2019, 04:44 PM
1use ilftrain;
2
3IF EXISTS (
4 SELECT * FROM INFORMATION_SCHEMA.TABLES
5 WHERE TABLE_NAME = 'Project_FACT_SALES'
6 )
7 DROP TABLE Project_FACT_SALES
8 IF EXISTS (
9 SELECT * FROM INFORMATION_SCHEMA.TABLES
10 WHERE TABLE_NAME = 'Project_DIM_EMPLOYEE'
11 )
12 DROP TABLE Project_DIM_EMPLOYEE
13
14 IF EXISTS (
15 SELECT * FROM INFORMATION_SCHEMA.TABLES
16 WHERE TABLE_NAME = 'Project_DIM_PROJECT_TYPE'
17 )
18 DROP TABLE Project_DIM_PROJECT_TYPE
19
20 IF EXISTS (
21 SELECT * FROM INFORMATION_SCHEMA.TABLES
22 WHERE TABLE_NAME = 'Project_DIM_LOCATION'
23 )
24 DROP TABLE Project_DIM_LOCATION
25
26 IF EXISTS (
27 SELECT * FROM INFORMATION_SCHEMA.TABLES
28 WHERE TABLE_NAME = 'Project_DIM_ACTIVITY_DESCRIPTION'
29 )
30 DROP TABLE Project_DIM_ACTIVITY_DESCRIPTION
31
32 IF EXISTS (
33 SELECT * FROM INFORMATION_SCHEMA.TABLES
34 WHERE TABLE_NAME = 'Project_DIM_TIME'
35 )
36 DROP TABLE Project_DIM_TIME
37
38 IF EXISTS (
39 SELECT * FROM INFORMATION_SCHEMA.TABLES
40 WHERE TABLE_NAME = 'Project_DIM_COUNTRY'
41 )
42 DROP TABLE Project_DIM_COUNTRY
43
44
45
46CREATE TABLE Project_FACT_SALES(
47Employee_ID int NOT NULL,
48Time_ID varchar(11) NOT NULL,
49Activity_description_ID int NOT NULL,
50Location_ID int NOT NULL,
51Project_type_ID int NOT NULL,
52Country_ID int not null,
53Income int NOT NULL,
54Profit int NOT NULL,
55Cost int NOT NULL)
56
57CREATE TABLE Project_DIM_TIME(
58Time_ID varchar(11) NOT NULL,
59Rok INT NOT NULL,
60Miesiac INT NOT NULL,
61[Dzien miesiaca] INT NOT NULL);
62
63CREATE TABLE Project_DIM_LOCATION(
64Location_ID INT not null,
65LocationName VARCHAR(255) NOT NULL)
66
67CREATE TABLE Project_DIM_ACTIVITY_DESCRIPTION(
68Activity_description_ID INT NOT NULL,
69Activity_descriptionText Varchar(255) NOT NULL)
70
71CREATE TABLE Project_DIM_PROJECT_TYPE(
72Project_type_ID INT NOT NULL,
73Project_type_name VARCHAR(255) NOT NULL)
74
75CREATE TABLE Project_DIM_EMPLOYEE(
76Employee_ID int not null,
77Employee_name VARCHAR(255) not null,
78Employee_position VARCHAR(255) not null)
79
80CREATE TABLE Project_DIM_COUNTRY(
81Country_ID int not null,
82Country_name VARCHAR(255) not null)
83
84
85INSERT INTO
86 Project_DIM_EMPLOYEE(Employee_ID, Employee_name, Employee_position)
87 SELECT EMPLOYEENUMBER, NAME1, POSITION
88 FROM EMPLOYEE
89
90INSERT INTO
91 Project_DIM_EMPLOYEE(Employee_ID, Employee_name, Employee_position)
92 VALUES(0, 'Nieznane', 'Nieznane')
93
94INSERT INTO
95 Project_DIM_LOCATION(Location_ID, LocationName)
96 SELECT LOCATIONNAME, DESCRIPTION
97 FROM LOCATION
98
99INSERT INTO
100 Project_DIM_PROJECT_TYPE(Project_type_ID, Project_type_name)
101 SELECT PROJECTNAME, DESCRIPTION
102 FROM PROJECT
103
104INSERT INTO
105 Project_DIM_COUNTRY(Country_ID, Country_name)
106 SELECT COUNTRYNUMBER, NAME
107 FROM COUNTRY
108
109INSERT INTO
110 Project_DIM_ACTIVITY_DESCRIPTION(Activity_description_ID, Activity_descriptionText)
111 SELECT ROWID, ACTIVITYTEXT
112 FROM JOBENTRY
113
114INSERT INTO
115 Project_DIM_TIME(Time_ID,Rok, Miesiac, [Dzien miesiaca])
116 SELECT DISTINCT
117 ENTRYDATE,
118 CONVERT(INT,SUBSTRING(ENTRYDATE,1,4)),
119 CONVERT(INT,SUBSTRING(ENTRYDATE,6,2)),
120 CONVERT(INT,SUBSTRING(ENTRYDATE,9,2))
121 FROM JOBENTRY
122
123insert into
124 Project_FACT_SALES(Employee_ID,Time_ID,Activity_description_ID,Location_ID,
125 Project_type_ID, Country_ID, Income, Profit, Cost)
126 SELECT EMPLOYEENUMBER , ENTRYDATE, A.ROWID, A.LOCATIONNAME, A.PROJECTNAME,
127 F.COUNTRYNUMBER, A.BILLINGPRICEREGCURRENCY, A.COSTPRICEREG,
128 A.BILLINGPRICEREGCURRENCY-A.COSTPRICEREG
129 FROM JOBENTRY A
130 LEFT OUTER JOIN JOBHEADER B ON A.JOBNUMBER=B.JOBNUMBER
131 LEFT OUTER JOIN COUNTRY F ON B.COUNTRY=F.COUNTRYNUMBER
132
133ALTER TABLE Project_DIM_COUNTRY
134 ADD PRIMARY KEY(Country_ID)
135
136ALTER TABLE Project_DIM_LOCATION
137 ADD PRIMARY KEY(Location_ID)
138
139ALTER TABLE Project_DIM_ACTIVITY_DESCRIPTION
140 ADD PRIMARY KEY(Activity_description_ID)
141
142ALTER TABLE Project_DIM_PROJECT_TYPE
143 ADD PRIMARY KEY(Project_type_ID)
144
145ALTER TABLE Project_DIM_EMPLOYEE
146 ADD PRIMARY KEY(Employee_ID)
147
148ALTER TABLE Project_DIM_TIME
149 ADD PRIMARY KEY(Time_ID)
150
151ALTER TABLE Project_FACT_SALES
152 ADD FOREIGN KEY (Employee_ID) REFERENCES Project_DIM_EMPLOYEE(Employee_ID)
153ALTER TABLE Project_FACT_SALES
154 ADD FOREIGN KEY(Time_ID) REFERENCES Project_DIM_TIME(Time_ID)
155ALTER TABLE Project_FACT_SALES
156 ADD FOREIGN KEY(Location_ID) REFERENCES Project_DIM_LOCATION(Location_ID)
157ALTER TABLE Project_FACT_SALES
158 ADD FOREIGN KEY(Activity_description_ID) REFERENCES Project_DIM_ACTIVITY_DESCRIPTION(Activity_description_ID)
159ALTER TABLE Project_FACT_SALES
160 ADD FOREIGN KEY(Project_type_ID) REFERENCES Project_DIM_PROJECT_TYPE(Project_type_ID)
161ALTER TABLE Project_FACT_SALES
162 ADD FOREIGN KEY(Country_ID) REFERENCES Project_DIM_COUNTRY(Country_ID)