AHRc3gEL

1USE AdventureWorks2014
2GO
3CREATE SCHEMA Sliwinska
4
5
6
7
8
9
10
11
12USE AdventureWorks2014
13GO
14CREATE TABLE Sliwinska.DIM_CUSTOMER(CustomerID INT NOT NULL, FirstName NVARCHAR(50) NULL, LastName NVARCHAR(50) NULL, TerritoryName NVARCHAR(50) NOT NULL, CountryRegionCode NVARCHAR(3) NOT NULL, [Group] NVARCHAR(50) NOT NULL)
15
16
17USE AdventureWorks2014
18GO
19CREATE TABLE Sliwinska.DIM_PRODUCT(ProductID INT NOT NULL, [Name] NVARCHAR(50) NOT NULL, ListPrice MONEY NOT NULL, Color NVARCHAR(15) NULL, SubCategoryName NVARCHAR(50) NULL, CategoryName NVARCHAR(50) NULL)
20
21USE AdventureWorks2014
22GO
23CREATE TABLE Sliwinska.DIM_SALESPERSON(SalesPersonID INT NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, Title NVARCHAR(8) NULL, Gender NVARCHAR(1) NOT NULL, CountryRegionCode NVARCHAR(3) NULL, [Group] NVARCHAR(50) NULL, Age INT NOT NULL)
24
25
26USE AdventureWorks2014
27GO
28CREATE TABLE Sliwinska.FACT_SALES(ProductID INT NOT NULL, CustomerID INT NOT NULL, SalesPersonID INT NULL, OrderDate INT NOT NULL, ShipDate INT NULL, 
29OrderQty SMALLINT NOT NULL, UnitPrice MONEY NOT NULL, UnitPriceDiscount MONEY NOT NULL, LineTotal NUMERIC(38, 6) NOT NULL)
30
31
32USE AdventureWorks2014
33GO
34INSERT INTO 
35	Sliwinska.DIM_CUSTOMER(CustomerID, FirstName, LastName, TerritoryName, CountryRegionCode, [Group])
36	SELECT CustomerID, FirstName, LastName, ST.[Name] AS TerritoryName, CountryRegionCode, [Group]
37	FROM Sales.Customer C
38	LEFT JOIN Person.Person P
39	ON P.BusinessEntityID =  C.PersonID
40	LEFT JOIN Sales.SalesTerritory ST
41	ON ST.TerritoryID = C.TerritoryID
42
43
44	USE AdventureWorks2014
45GO
46INSERT INTO 
47	Sliwinska.DIM_PRODUCT(ProductID, [Name], ListPrice, Color, SubCategoryName, CategoryName)
48	SELECT P.ProductID, P.[Name], P.ListPrice, P.Color, PS.[Name] AS SubCategoryName, PC.[Name] AS CategoryName
49	FROM Production.Product P
50	LEFT JOIN Production.ProductSubcategory PS
51	ON PS.ProductSubcategoryID = P.ProductSubcategoryID
52	LEFT JOIN Production.ProductCategory PC
53	ON PS.ProductCategoryID = PC.ProductCategoryID
54
55
56USE AdventureWorks2014
57GO
58INSERT INTO
59	Sliwinska.FACT_SALES(ProductID, CustomerID, SalesPersonID, OrderDate, ShipDate, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal)
60	SELECT ProductID, CustomerID, SalesPersonID, 
61	CAST(CAST(YEAR(OrderDate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(OrderDate) AS VARCHAR(2)),2) +  RIGHT('0' + CAST(DAY(OrderDate) AS VARCHAR(2)), 2) AS INT) AS OrderDate, 
62	CAST(CAST(YEAR(ShipDate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(ShipDate) AS VARCHAR(2)),2) +  RIGHT('0' + CAST(DAY(ShipDate) AS VARCHAR(2)), 2) AS INT) AS ShipDate, 
63	OrderQty, UnitPrice, UnitPriceDiscount, LineTotal
64	FROM Sales.SalesOrderHeader OH
65	JOIN Sales.SalesOrderDetail OD
66	ON OH.SalesOrderID = OD.SalesOrderID
67
68
69USE AdventureWorks2014
70GO
71INSERT INTO
72	Sliwinska.DIM_SALESPERSON(SalesPersonID, FirstName, LastName, Title, Gender, CountryRegionCode, [Group], Age)
73SELECT SP.BusinessEntityID AS SalesPersonID, P.FirstName, P.LastName, P.Title, E.Gender, ST.CountryRegionCode, ST.[Group], DATEDIFF(yyyy, E.BirthDate, GETDATE())AS Age
74	FROM Sales.SalesPerson SP
75	LEFT JOIN Person.Person P
76	ON SP.BusinessEntityID = P.BusinessEntityID
77	LEFT JOIN HumanResources.Employee E
78	ON SP.BusinessEntityID = E.BusinessEntityID
79	LEFT JOIN Sales.SalesTerritory ST
80	ON SP.TerritoryID = ST.TerritoryID
81
82
83USE AdventureWorks2014
84ALTER TABLE Sliwinska.DIM_CUSTOMER
85ADD PRIMARY KEY (CustomerID)
86
87USE AdventureWorks2014
88ALTER TABLE Sliwinska.DIM_PRODUCT
89ADD PRIMARY KEY (ProductID)
90
91USE AdventureWorks2014
92ALTER TABLE Sliwinska.DIM_SALESPERSON
93ADD PRIMARY KEY (SalesPersonID)
94
95
96USE AdventureWorks2014
97GO
98ALTER TABLE Sliwinska.FACT_SALES
99ADD FactID int IDENTITY(1,1)
100ALTER TABLE Sliwinska.FACT_SALES
101ADD PRIMARY KEY (FactID)
102
103
104
105FACT_SALES:
106ALTER TABLE Sliwinska.FACT_SALES
107ADD FOREIGN KEY (CustomerID) REFERENCES Sliwinska.DIM_CUSTOMER(CustomerID)
108
109ALTER TABLE Sliwinska.FACT_SALES
110ADD FOREIGN KEY (ProductID) REFERENCES Sliwinska.DIM_PRODUCT(ProductID)
111
112
113
114
115ALTER TABLE Sliwinska.FACT_SALES 
116ADD FOREIGN KEY (SalesPersonID) REFERENCES Sliwinska.DIM_SALESPERSON(SalesPersonID)
117
118--5
119
120
121IF (EXISTS (
122SELECT * FROM INFORMATION_SCHEMA.TABLES
123WHERE TABLE_SCHEMA = 'Sliwinska'
124AND TABLE_NAME = 'DIM_CUSTOMER'))
125DROP TABLE Sliwinska.DIM_CUSTOMER
126
127
128
129IF (EXISTS ( 
130SELECT * FROM INFORMATION_SCHEMA.TABLES
131WHERE TABLE_SCHEMA = 'Sliwinska'
132AND TABLE_NAME = 'DIM_PRODUCT'))
133DROP TABLE Sliwinska.DIM_PRODUCT
134
135IF (EXISTS ( 
136SELECT * FROM INFORMATION_SCHEMA.TABLES
137WHERE TABLE_SCHEMA = 'Sliwinska'
138AND TABLE_NAME = 'DIM_SALESPERSON'))
139DROP TABLE Sliwinska.DIM_SALESPERSON
140
141IF (EXISTS ( 
142SELECT * FROM INFORMATION_SCHEMA.TABLES
143WHERE TABLE_SCHEMA = 'Sliwinska'
144AND TABLE_NAME = 'FACT_SALES'))
145DROP TABLE Sliwinska.FACT_SALES
146
147
148
149--7
150
151IF (EXISTS ( 
152SELECT * FROM INFORMATION_SCHEMA.TABLES
153WHERE TABLE_SCHEMA = 'Sliwinska'
154AND TABLE_NAME = 'DIM_TIME'))
155DROP TABLE Sliwinska.DIM_TIME
156
157IF (EXISTS ( 
158SELECT * FROM INFORMATION_SCHEMA.TABLES
159WHERE TABLE_SCHEMA = 'Sliwinska'
160AND TABLE_NAME = 'Months'))
161DROP TABLE Sliwinska.Months
162
163IF (EXISTS ( 
164SELECT * FROM INFORMATION_SCHEMA.TABLES
165WHERE TABLE_SCHEMA = 'Sliwinska'
166AND TABLE_NAME = 'WeekDay'))
167DROP TABLE Sliwinska.[WeekDay]
168
169CREATE TABLE Sliwinska.DIM_TIME([Time] INT NOT NULL PRIMARY KEY, Rok INT NOT NULL, Miesiac NVARCHAR(15) NOT NULL, [Dzien tygodnia] NVARCHAR(15) NOT NULL, [Dzien miesiaca] INT NULL)
170
171CREATE TABLE Sliwinska.Months(
172MonthId INT NOT NULL PRIMARY KEY IDENTITY(1, 1), 
173[MonthName] NVARCHAR(15) NOT NULL
174)
175INSERT INTO Sliwinska.Months ([MonthName]) 
176VALUES ('January'), ('February'), ('March'), ('April'), ('May'), ('June'), ('July'), ('August'), ('September'), ('October'), ('November'), ('December')
177GO
178CREATE TABLE Sliwinska.[WeekDay](
179weekDayId INT NOT NULL PRIMARY KEY IDENTITY(1, 1), 
180[weekDayName] NVARCHAR(15) NOT NULL
181)
182INSERT INTO Sliwinska.[WeekDay](WeekDayName)
183VALUES  ('Sunday'), ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday')
184
185INSERT INTO
186Sliwinska.DIM_TIME([Time], Rok, Miesiac, [Dzien tygodnia] ,[Dzien miesiaca])
187(SELECT DISTINCT OrderDate ,LEFT(OrderDate, 4), Sliwinska.Months.[MonthName], Sliwinska.[WeekDay].[WeekDayName], RIGHT(OrderDate, 2) FROM
188(SELECT OrderDate FROM Sliwinska.FACT_SALES) AS FS
189LEFT JOIN Sliwinska.Months
190ON LEFT(OrderDate, 6)- LEFT(OrderDate, 4)*100 = Sliwinska.Months.MonthId
191LEFT JOIN Sliwinska.[WeekDay]
192ON (DATEPART(dw, CONVERT(DATE,  CAST(OrderDate AS VARCHAR(8)), 112)) = Sliwinska.[WeekDay].weekDayId)
193UNION
194SELECT DISTINCT ShipDate ,LEFT(ShipDate, 4), Sliwinska.Months.[MonthName], Sliwinska.[WeekDay].[WeekDayName], RIGHT(ShipDate, 2) FROM
195(SELECT ShipDate FROM Sliwinska.FACT_SALES) AS SD
196LEFT JOIN Sliwinska.Months
197ON LEFT(ShipDate, 6)- LEFT(ShipDate, 4)*100 = Sliwinska.Months.MonthId
198LEFT JOIN Sliwinska.[WeekDay]
199ON (DATEPART(dw, CONVERT(DATE,  CAST(ShipDate AS VARCHAR(8)), 112)) = Sliwinska.[WeekDay].weekDayId))
200
201
202ALTER TABLE Sliwinska.FACT_SALES 
203ADD FOREIGN KEY (OrderDate) REFERENCES Sliwinska.DIM_TIME([Time])
204
205ALTER TABLE Sliwinska.FACT_SALES 
206ADD FOREIGN KEY (ShipDate) REFERENCES Sliwinska.DIM_TIME([Time])
207
208
209
210
211UPDATE Sliwinska.DIM_PRODUCT
212SET Color = 'Unknown'
213WHERE Color IS NULL
214
215UPDATE Sliwinska.DIM_PRODUCT
216SET SubCategoryName = 'Unknown'
217WHERE SubCategoryName IS NULL
218
219
220
221UPDATE Sliwinska.DIM_SALESPERSON
222SET CountryRegionCode = '000'
223WHERE CountryRegionCode IS NULL
224
225UPDATE Sliwinska.DIM_SALESPERSON
226SET [Group] = 'Unknown'
227WHERE [Group] IS NULL