create table Dudek.DIM_CUSTOMER(
CustomerID int not null,
FirstName nvarchar(50) not null,
LastName nvarchar(50) not null,
Title nvarchar(16),
City nvarchar(30) not null,
TerritoryName nvarchar(50) not null,
CountryRegionCode nvarchar(3) not null,
[Group] nvarchar(50) not null
)

INSERT INTO Dudek.DIM_CUSTOMER(
	CustomerID,
	FirstName,
	LastName,
	Title,
	City,
	TerritoryName,
	CountryRegionCode,
	[Group]
)
SELECT
	CustomerID,
	FirstName,
	LastName,
	Title,
	City,
	Name,
	CountryRegionCode,
	[Group]
FROM
	AdventureWorks2017.Sales.Customer
	JOIN AdventureWorks2017.Person.Person ON Customer.PersonID = Person.BusinessEntityID
	JOIN AdventureWorks2017.Sales.SalesTerritory ON Customer.TerritoryID = SalesTerritory.TerritoryID
	JOIN AdventureWorks2017.Person.BusinessEntityAddress on Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
	JOIN AdventureWorks2017.Person.Address on BusinessEntityAddress.AddressID = Address.AddressID;

create table Dudek.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,
[Weight] decimal(8,2),
Size nvarchar(5),
)
--DODAC ISPURCHASED
INSERT INTO Dudek.DIM_PRODUCT(
	ProductID,
	Name,
	ListPrice,
	Color,
	SubCategoryName,
	CategoryName,
	Weight,
	Size
)
SELECT
	Product.ProductID,
	Product.Name,
	ListPrice,
	Color,
	ProductSubcategory.Name,
	ProductCategory.Name,
	Weight,
	Size
FROM
	AdventureWorks2017.Production.Product
	LEFT JOIN AdventureWorks2017.Production.ProductSubcategory ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
	LEFT JOIN AdventureWorks2017.Production.ProductCategory ON ProductSubcategory.ProductCategoryID = ProductCategory.ProductCategoryID
GROUP BY Product.ProductID, Product.Name, ListPrice, Color, ProductSubcategory.Name, ProductCategory.Name, Weight, Size;


create table Dudek.DIM_SALESPERSON(
SalesPersonID int not null,
FirstName nvarchar(50) not null,
LastName nvarchar(50) not null,
Title nvarchar(16),
Gender nchar(1),
CountryRegionCode nvarchar(3),
[Group] nvarchar(50)
)

INSERT INTO Dudek.DIM_SALESPERSON(
	SalesPersonID,
	FirstName,
	LastName,
	Title,
	Gender,
	CountryRegionCode,
	[Group]
)
SELECT
	SalesPerson.BusinessEntityID,
	FirstName,
	LastName,
	Title,
	Gender,
	CountryRegionCode,
	[Group]
FROM
AdventureWorks2017.Sales.SalesPerson JOIN AdventureWorks2017.HumanResources.Employee on SalesPerson.BusinessEntityID = Employee.BusinessEntityID
JOIN AdventureWorks2017.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
LEFT JOIN AdventureWorks2017.Sales.SalesTerritory on SalesPerson.TerritoryID = SalesTerritory.TerritoryID;

create table Dudek.FACT_SALES(
ProductID int not null,
CustomerID int not null,
SalesPersonID int,
OrderDate int not null,
ShipDate int,
OrderQty smallint not null,
UnitPrice MONEY not null,
UnitPriceDiscount MONEY not null,
LineTotal as UnitPrice * (1 - UnitPriceDiscount) * OrderQty
);

INSERT INTO Dudek.FACT_SALES(
	ProductID,
	CustomerID,
	SalesPersonID,
	OrderDate,
	ShipDate,
	OrderQty,
	UnitPrice,
	UnitPriceDiscount
)
SELECT
	ProductID,
	CustomerID,
	SalesPersonID,
	CAST(
		CAST(DATEPART(YEAR, OrderDate) AS nvarchar) +
		(CASE
			WHEN DATEPART(MONTH, OrderDate) < 10 THEN '0' + CAST(DATEPART(MONTH, OrderDate) AS nvarchar)
			ELSE CAST(DATEPART(MONTH, OrderDate) AS nvarchar)
		END) +
		(CASE
			WHEN DATEPART(DAY, OrderDate) < 10 THEN '0' + CAST(DATEPART(DAY, OrderDate) AS nvarchar)
			ELSE CAST(DATEPART(DAY, OrderDate) AS nvarchar)
		END) AS int
	),
	CAST(
		CAST(DATEPART(YYYY, ShipDate) AS nvarchar) +
		(CASE
			WHEN DATEPART(MONTH, ShipDate) < 10 THEN '0' + CAST(DATEPART(MONTH, ShipDate) AS nvarchar)
			ELSE CAST(DATEPART(MONTH, ShipDate) AS nvarchar)
		END) +
		(CASE
			WHEN DATEPART(DAY, ShipDate) < 10 THEN '0' + CAST(DATEPART(DAY, ShipDate) AS nvarchar)
			ELSE CAST(DATEPART(DAY, ShipDate) AS nvarchar)
		END) AS int
	),
	OrderQty,
	UnitPrice,
	UnitPriceDiscount
FROM
	AdventureWorks2017.Sales.SalesOrderHeader
	JOIN AdventureWorks2017.Sales.SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;