QntgtQLr

1-- A B
2IF EXISTS (
3	SELECT * 
4	FROM INFORMATION_SCHEMA.TABLES 
5	WHERE TABLE_SCHEMA = 'Wojtaszek' AND TABLE_NAME = 'MONTH_NAME'
6) DROP TABLE Wojtaszek.MONTH_NAME
7
8IF EXISTS (
9	SELECT * 
10	FROM INFORMATION_SCHEMA.TABLES 
11	WHERE TABLE_SCHEMA = 'Wojtaszek' AND TABLE_NAME = 'DAY_NAME'
12) DROP TABLE Wojtaszek.DAY_NAME
13
14IF EXISTS (
15	SELECT * 
16	FROM INFORMATION_SCHEMA.TABLES 
17	WHERE TABLE_SCHEMA = 'Wojtaszek' AND TABLE_NAME = 'DIM_TIME'
18) DROP TABLE Wojtaszek.DIM_TIME
19
20CREATE TABLE Wojtaszek.MONTH_NAME (
21	MonthNameID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
22	Name NVARCHAR(50)
23);
24
25CREATE TABLE Wojtaszek.DAY_NAME (
26	DayNameID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
27	Name NVARCHAR(50)
28);
29
30INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Styczeń');
31INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Luty');
32INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Marzec');
33INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Kwiecień');
34INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Maj');
35INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Czerwiec');
36INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Lipiec');
37INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Sierpień');
38INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Wrzesień');
39INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Październik');
40INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Listopad');
41INSERT INTO	Wojtaszek.MONTH_NAME VALUES ('Grudzień');
42
43INSERT INTO	Wojtaszek.DAY_NAME VALUES ('Poniedziałek');
44INSERT INTO	Wojtaszek.DAY_NAME VALUES ('Wtorek');
45INSERT INTO	Wojtaszek.DAY_NAME VALUES ('Åšroda');
46INSERT INTO	Wojtaszek.DAY_NAME VALUES ('Czwartek');
47INSERT INTO	Wojtaszek.DAY_NAME VALUES ('PiÄ…tek');
48INSERT INTO	Wojtaszek.DAY_NAME VALUES ('Sobota');
49INSERT INTO	Wojtaszek.DAY_NAME VALUES ('Niedziela');
50
51CREATE TABLE Wojtaszek.DIM_TIME (
52	PK_TIME INT NOT NULL PRIMARY KEY,
53	dt_year INT,
54	MonthNameID INT,
55	DayNameID INT,
56	dt_day INT,
57	FOREIGN KEY (DayNameID) REFERENCES Wojtaszek.DAY_NAME(DayNameID),
58	FOREIGN KEY (MonthNameID) REFERENCES Wojtaszek.MONTH_NAME(MonthNameID)
59);
60
61
62INSERT INTO Wojtaszek.DIM_TIME
63SELECT
64   	sourceTable.Dat,
65   	DATEPART(YEAR, CAST(sourceTable.Dat AS nvarchar)),
66	(SELECT Name FROM Wojtaszek.MONTH_NAME WHERE MonthNameID = DATEPART(MONTH, CAST(sourceTable.Dat AS nvarchar))) MonthNameID,
67	(SELECT Name FROM Wojtaszek.DAY_NAME WHERE DayNameID =  DATEPART(WEEKDAY, (CAST(sourceTable.Dat AS nvarchar)))) DayNameID,
68	DAY(CAST(sourceTable.Dat AS nvarchar))
69FROM (
70   	SELECT OrderDate Dat
71   	FROM Wojtaszek.FACT_SALES
72   	UNION
73   	SELECT ShipDate Dat
74   	FROM Wojtaszek.FACT_SALES
75) sourceTable
76
77ALTER TABLE Wojtaszek.FACT_SALES
78ADD CONSTRAINT FK_ProductID_FS
79FOREIGN KEY (OrderDate) REFERENCES Wojtaszek.DIM_TIME(PK_TIME);
80
81ALTER TABLE Wojtaszek.FACT_SALES
82ADD CONSTRAINT FK_SalesPersonID_FS
83FOREIGN KEY (ShipDate) REFERENCES Wojtaszek.DIM_TIME(PK_TIME);
84
85-- C
86UPDATE Wojtaszek.DIM_PRODUCT SET Color = 'Unknown' WHERE Color IS NULL
87UPDATE Wojtaszek.DIM_PRODUCT SET SubCategoryName = 'Unknown' WHERE SubCategoryName IS NULL
88
89-- D
90UPDATE Wojtaszek.DIM_SALESPERSON SET CountryRegionCode = '000' WHERE CountryRegionCode IS NULL
91UPDATE Wojtaszek.DIM_SALESPERSON SET "Group" = 'Unknown' WHERE "Group" IS NULL