USE szukam
GO
CREATE SCHEMA Gadzalinski AUTHORIZATION dbo
GO


USE szukam
GO
CREATE TABLE Gadzalinski.DIM_CONDITIONS(
conditionsID INT NOT NULL IDENTITY (1,1),
light_condition varchar(10),
weather varchar(20),
road_surface varchar(16),
special_conditions varchar(16)
);
GO


USE szukam
GO
CREATE TABLE Gadzalinski.DIM_CASUALTY(
casualtyID INT NOT NULL IDENTITY(1,1),
casualty_class varchar(14),
casualty_sex varchar(8),
casualty_age_band varchar(7),
age SMALLINT,
casualty_severity varchar(9),
mode_of_travel varchar(19)
);
GO


USE szukam
GO
CREATE TABLE Gadzalinski.DIM_LOCATION(
locationID INT NOT NULL IDENTITY(1,1),
Warcode varchar(9),
ward_name varchar(30),
attendantLocation VARCHAR(66),
spatial_accuracy varchar(7),
junction_detail varchar(15)
);
GO


USE szukam
GO
CREATE TABLE Gadzalinski.DIM_ROAD(
roadID INT NOT NULL IDENTITY(1,1),
highway varchar(6),
road_class1 varchar(12),
road_number INT,
road_type varchar(12),
speed_limit varchar(11),
junction_control varchar(23),
road_class2 varchar(17),
road_number2 INT
);
GO


USE szukam
GO
CREATE TABLE Gadzalinski.FACT_ACCIDENT(
accidentID INT NOT NULL IDENTITY(1,1),
casualtyID INT,
conditionID INT,
roadID INT,
locationID INT,
accidentDate INT,
number_of_casualties INT,
number_of_vehicles INT,
reference varchar(11)
);
GO


USE szukam
GO
CREATE TABLE Gadzalinski.DIM_DATE(
[Time] INT CONSTRAINT PK_TIME PRIMARY KEY,
[Year] INT,
[Month] VARCHAR(20),
[Week_day] VARCHAR(20),
[Day] INT
);
GO

USE szukam
GO
CREATE TABLE Gadzalinski.[Months](
Number int PRIMARY KEY,
Name VARCHAR(9) NOT NULL
);
GO

USE szukam
GO
CREATE TABLE Gadzalinski.[Days](
Number int PRIMARY KEY,
Name VARCHAR(9) NOT NULL
);
GO



----------------------------------------------------------

--nowe
USE szukam
GO
INSERT INTO Gadzalinski.DIM_CONDITIONS(light_condition,weather,road_surface,special_conditions)
SELECT DISTINCT
[Light Condition Band],
[Weather],
[Road Surface],
[Special Conditions]
FROM [Road_Collision_Casualties_in_Camden_-_last_3_years]
GO
--nowe



USE szukam
GO
INSERT INTO Gadzalinski.DIM_CASUALTY(casualty_class,casualty_sex,casualty_age_band,age,casualty_severity,mode_of_travel)
SELECT DISTINCT
[Casualty Class],
[Casualty Sex],
[Casualty Age Band],
[Casualty Age],
[Casualty Severity],
[Mode Of Travel]
FROM [Road_Collision_Casualties_in_Camden_-_last_3_years]
GO



USE szukam
GO
INSERT INTO Gadzalinski.DIM_LOCATION(Warcode,ward_name,attendantLocation,spatial_accuracy,junction_detail)
SELECT DISTINCT
[Ward Code],
[Ward Name],
[Attendant Location],
[Spatial Accuracy],
[Junction Detail]
FROM [Road_Collision_Casualties_in_Camden_-_last_3_years]
GO





USE szukam
GO
INSERT INTO Gadzalinski.DIM_ROAD(highway,road_class1,road_number,road_type,speed_limit,junction_control,road_class2,road_number2)
SELECT DISTINCT
[Highway],
[Road Class 1],
[Road Number 1],
[Road Type],
[Speed Limit],
[Junction Control],
[Road Class 2],
[Road Number 2]
FROM [Road_Collision_Casualties_in_Camden_-_last_3_years]
GO






USE szukam
GO
INSERT INTO Gadzalinski.FACT_ACCIDENT(casualtyID,conditionID,roadID,locationID,accidentDate,number_of_casualties,number_of_vehicles,reference)
SELECT
CAS.casualtyID,
CON.conditionsID,
ROA.roadID,
LOC.locationID,
    CAST(CONCAT(datepart(YY, [Road_Collision_Casualties_in_Camden_-_last_3_years].[Date]),
            CASE WHEN datepart(M, [Road_Collision_Casualties_in_Camden_-_last_3_years].[Date]) < 10 THEN '0' END, datepart(M, [Road_Collision_Casualties_in_Camden_-_last_3_years].[Date]),
            CASE WHEN datepart(D, [Road_Collision_Casualties_in_Camden_-_last_3_years].[Date]) < 10 THEN '0' END, datepart(D, [Road_Collision_Casualties_in_Camden_-_last_3_years].[Date])) AS int),
[Number Of Casualties],
[Number Of Vehicles],
[Reference]
FROM [Road_Collision_Casualties_in_Camden_-_last_3_years], Gadzalinski.DIM_CONDITIONS CON, Gadzalinski.DIM_CASUALTY CAS, Gadzalinski.DIM_LOCATION LOC, Gadzalinski.DIM_ROAD ROA
WHERE
ROA.highway= [Road_Collision_Casualties_in_Camden_-_last_3_years].Highway AND
ROA.road_class1=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Road Class 1] AND
ROA.road_number=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Road Number 1] AND
ROA.road_type=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Road Type] AND
ROA.speed_limit=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Speed Limit] AND
ROA.junction_control=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Junction Control] AND
ROA.road_class2=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Road Class 2] AND
ROA.road_number2=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Road Number 2] AND

LOC.Warcode=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Ward Code] AND
LOC.ward_name=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Ward Name] AND
LOC.attendantLocation=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Attendant Location] AND
LOC.spatial_accuracy=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Spatial Accuracy] AND
LOC.junction_detail= [Road_Collision_Casualties_in_Camden_-_last_3_years].[Junction Detail] AND

CON.light_condition=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Light Condition Band] AND
CON.weather=[Road_Collision_Casualties_in_Camden_-_last_3_years].Weather AND
CON.road_surface=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Road Surface] AND
CON.special_conditions= [Road_Collision_Casualties_in_Camden_-_last_3_years].[Special Conditions] AND

CAS.casualty_class=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Casualty Class] AND
CAS.casualty_sex=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Casualty Sex] AND
CAS.casualty_age_band=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Casualty Age Band] AND
CAS.age=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Casualty Age] AND
CAS.casualty_severity=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Casualty Severity] AND
CAS.mode_of_travel=[Road_Collision_Casualties_in_Camden_-_last_3_years].[Mode Of Travel]

GO



USE szukam
GO
INSERT INTO Gadzalinski.Months VALUES
(1,'January'),
(2,'February'),
(3,'March'),
(4,'April'),
(5,'May'),
(6,'June'),
(7,'July'),
(8,'August'),
(9,'September'),
(10,'October'),
(11,'November'),
(12,'December');
GO

USE szukam
GO
INSERT INTO Gadzalinski.[Days] VALUES
(1,'Sunday'),
(2,'Monday'),
(3,'Tuesday'),
(4,'Wednesday'),
(5,'Thursday'),
(6,'Friday'),
(7,'Saturday');
GO

USE szukam
GO
INSERT INTO Gadzalinski.DIM_DATE
SELECT DISTINCT
CAST(CONCAT(datepart(YY, [Date]),
                    CASE WHEN datepart(M, [Date]) < 10 THEN '0' END,
                    datepart(M, [Date]),
                    CASE WHEN datepart(D, [Date]) < 10 THEN '0' END,
                    datepart(D, [Date]))    AS int),
    DATEPART(YY, [Date]), MON.[Name], D.[Name], DATEPART(D, [Date])
FROM [Road_Collision_Casualties_in_Camden_-_last_3_years]
JOIN Gadzalinski.Months MON ON DATEPART(M,[Date])=MON.Number
JOIN Gadzalinski.[Days] D ON DATEPART(DW,[Date])=D.Number
GO





--------
USE szukam
GO
ALTER TABLE Gadzalinski.DIM_CONDITIONS
ADD CONSTRAINT PK_DIM_CONDITIONS PRIMARY KEY(conditionsID);
GO

ALTER TABLE Gadzalinski.DIM_CASUALTY
ADD CONSTRAINT PK_DIM_CASUALTY PRIMARY KEY (casualtyID);
GO

ALTER TABLE Gadzalinski.DIM_LOCATION
ADD CONSTRAINT PK_DIM_LOCATION PRIMARY KEY (locationID);
GO

ALTER TABLE Gadzalinski.DIM_ROAD
ADD CONSTRAINT PK_DIM_ROAD PRIMARY KEY(roadID);
GO

ALTER TABLE Gadzalinski.FACT_ACCIDENT
ADD CONSTRAINT PK_FACT_ACCIDENT PRIMARY KEY (accidentID),
CONSTRAINT FK_ACCIDENT_CASUALTYID FOREIGN KEY (casualtyID) REFERENCES Gadzalinski.DIM_CASUALTY(casualtyID),
CONSTRAINT FK_CONDITION_CONDITIONID FOREIGN KEY (conditionID) REFERENCES Gadzalinski.DIM_CONDITIONS(conditionsID),
CONSTRAINT FK_ROAD_ROADID FOREIGN KEY(roadID) REFERENCES Gadzalinski.DIM_ROAD(roadID),
CONSTRAINT FK_LOCATION_LOCATIONID FOREIGN KEY(locationID) REFERENCES Gadzalinski.DIM_LOCATION(locationID),
CONSTRAINT FK_DATE_ACCIDENTDATE FOREIGN KEY ([accidentDate]) REFERENCES Gadzalinski.DIM_DATE([Time]);
GO


--

USE szukam
GO
UPDATE Gadzalinski.DIM_CONDITIONS
SET light_condition = SUBSTRING(light_condition,3,15)
WHERE light_condition LIKE '[0-9]%';
GO

GO
UPDATE Gadzalinski.DIM_CONDITIONS
SET weather = SUBSTRING(weather,3,30)
WHERE weather LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_CONDITIONS
SET road_surface = SUBSTRING(road_surface,3,20)
WHERE road_surface LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_CONDITIONS
SET special_conditions = SUBSTRING(special_conditions,3,20)
WHERE special_conditions LIKE '[0-9]%';
GO






USE szukam
GO
UPDATE Gadzalinski.DIM_CASUALTY
SET casualty_class = SUBSTRING(casualty_class,3,15)
WHERE casualty_class LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_CASUALTY
SET casualty_sex= SUBSTRING(casualty_sex,2,9)
WHERE casualty_sex LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_CASUALTY
SET casualty_severity = SUBSTRING(casualty_severity,3,15)
WHERE casualty_severity LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_CASUALTY
SET mode_of_travel= SUBSTRING(mode_of_travel,3,30)
WHERE mode_of_travel LIKE '[0-9]%';
GO




USE szukam
GO
UPDATE Gadzalinski.DIM_LOCATION
SET Warcode='UNKOWN'
WHERE Warcode=' ';
GO

UPDATE Gadzalinski.DIM_LOCATION
SET ward_name='Unkown'
WHERE ward_name=' ';
GO

UPDATE Gadzalinski.DIM_LOCATION
SET spatial_accuracy='Unkown'
WHERE spatial_accuracy=' ';
GO

UPDATE Gadzalinski.DIM_LOCATION
SET junction_detail=SUBSTRING(junction_detail,2,25)
WHERE junction_detail LIKE '[0-9]%';
GO


USE szukam
GO
UPDATE Gadzalinski.DIM_ROAD
SET highway= SUBSTRING(highway,3,15)
WHERE highway LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_ROAD
SET road_type= SUBSTRING(road_type,2,20)
WHERE road_type LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_ROAD
SET speed_limit= SUBSTRING(speed_limit,3,15)
WHERE speed_limit LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_ROAD
SET junction_control= SUBSTRING(junction_control,3,45)
WHERE junction_control LIKE '[0-9]%';
GO

UPDATE Gadzalinski.DIM_ROAD
SET junction_control= SUBSTRING(junction_control,3,25)
WHERE junction_control LIKE '-%';
GO

UPDATE Gadzalinski.DIM_ROAD
SET road_class2= SUBSTRING(road_class2,3,17)
WHERE road_class2 LIKE '-%';
GO

-----





USE szukam
GO
IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ('FACT_ACCIDENT'))
DROP TABLE Gadzalinski.FACT_ACCIDENT;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ('DIM_CASUALTY'))
DROP TABLE Gadzalinski.DIM_CASUALTY;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ('DIM_CONDITIONS'))
DROP TABLE Gadzalinski.DIM_CONDITIONS;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ('DIM_ROAD'))
DROP TABLE Gadzalinski.DIM_ROAD;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE('DIM_LOCATION'))
DROP TABLE Gadzalinski.DIM_LOCATION;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE('DIM_DATE'))
DROP TABLE Gadzalinski.DIM_DATE;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE('Days'))
DROP TABLE Gadzalinski.Days;
GO

IF EXISTS (SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ('Months'))
DROP TABLE Gadzalinski.Months;