USE WideWorldImporters
GO

/* REQUIREMENT 1 */

CREATE TABLE dbo.FactOrders (
    CustomerKey    INT NOT NULL,
    CityKey        INT NOT NULL,
    ProductKey     INT NOT NULL,
    SalespersonKey INT NOT NULL,
	SupplierKey	   INT NOT NULL,
    DateKey        INT NOT NULL,
    Quantity       INT NOT NULL,
    UnitPrice      DECIMAL(18,2) NOT NULL,
    TaxRate        DECIMAL(18,3) NOT NULL,
    TotalBeforeTax DECIMAL(18,2) NOT NULL,
    TotalAfterTax  DECIMAL(18,2) NOT NULL
);

CREATE INDEX IX_FactOrders_CustomerKey ON dbo.FactOrders(CustomerKey);
CREATE INDEX IX_FactOrders_CityKey ON dbo.FactOrders(CityKey);
CREATE INDEX IX_FactOrders_ProductKey ON dbo.FactOrders(ProductKey);
CREATE INDEX IX_FactOrders_SalespersonKey ON dbo.FactOrders(SalespersonKey);
CREATE INDEX IX_FactOrders_SupplierKey ON dbo.FactOrders(SupplierKey);
CREATE INDEX IX_FactOrders_DateKey ON dbo.FactOrders(DateKey);


CREATE TABLE dbo.DimCities(
	CityKey INT NOT NULL,
	CityName NVARCHAR(50) NULL,
	StateProvCode NVARCHAR(5) NULL,
	StateProvName NVARCHAR(50) NULL,
	CountryName NVARCHAR(60) NULL,
	CountryFormalName NVARCHAR(60) NULL,
    CONSTRAINT PK_DimCities PRIMARY KEY CLUSTERED ( CityKey )
);

CREATE TABLE dbo.DimCustomers(
	CustomerKey INT NOT NULL,
	CustomerName NVARCHAR(100) NULL,
	CustomerCategoryName NVARCHAR(50) NULL,
	DeliveryCityName NVARCHAR(50) NULL,
	DeliveryStateProvCode NVARCHAR(5) NULL,
	DeliveryCountryName NVARCHAR(50) NULL,
	PostalCityName NVARCHAR(50) NULL,
	PostalStateProvCode NVARCHAR(5) NULL,
	PostalCountryName NVARCHAR(50) NULL,
	StartDate DATE NOT NULL,
	EndDate DATE NULL,
    CONSTRAINT PK_DimCustomers PRIMARY KEY CLUSTERED ( CustomerKey )
);

CREATE TABLE dbo.DimProducts(
	ProductKey INT NOT NULL,
	ProductName NVARCHAR(100) NULL,
	ProductColour NVARCHAR(20) NULL,
	ProductBrand NVARCHAR(50) NULL,
	ProductSize NVARCHAR(20) NULL,
	StartDate DATE NOT NULL,
	EndDate DATE NULL,
    CONSTRAINT PK_DimProducts PRIMARY KEY CLUSTERED ( ProductKey )
);

CREATE TABLE dbo.DimSalesPeople(
	SalespersonKey INT NOT NULL,
	FullName NVARCHAR(50) NULL,
	PreferredName NVARCHAR(50) NULL,
	LogonName NVARCHAR(50) NULL,
	PhoneNumber NVARCHAR(20) NULL,
	FaxNumber NVARCHAR(20) NULL,
	EmailAddress NVARCHAR(256) NULL,
    CONSTRAINT PK_DimSalesPeople PRIMARY KEY CLUSTERED (SalespersonKey )
);

CREATE TABLE dbo.DimSuppliers(
	SupplierKey INT NOT NULL,
	FullName NVARCHAR(100) NULL,
	PhoneNumber NVARCHAR(20) NULL,
	FaxNumber NVARCHAR(20) NULL,
	WebsiteURL NVARCHAR(256) NULL,
	StartDate DATE NOT NULL,
	EndDate DATE NULL,
    CONSTRAINT PK_DimSuppliers PRIMARY KEY CLUSTERED (SupplierKey )
);


CREATE TABLE dbo.DimDate (
    DateKey       INT NOT NULL,
    DateValue     DATE NOT NULL,
    Year          SMALLINT NOT NULL,
    Month         TINYINT NOT NULL,
    Day           TINYINT NOT NULL,
    Quarter       TINYINT NOT NULL,
    StartOfMonth  DATE NOT NULL,
    EndOfMonth    DATE NOT NULL,
    MonthName     VARCHAR(9) NOT NULL,
    DayOfWeekName VARCHAR(9) NOT NULL,    

    CONSTRAINT PK_DimDate PRIMARY KEY ( DateKey )
);
GO

/* REQUIREMENT 2 */

CREATE PROCEDURE dbo.InsertDimDate

@DateValue DATE

AS
BEGIN;
	INSERT INTO dbo.DimDate
	SELECT CAST( YEAR(@DateValue) * 10000 + MONTH(@DateValue) * 100 + DAY(@DateValue) AS INT),
	@DateValue,
	YEAR(@DateValue),
	MONTH(@DateValue),
	DAY(@DateValue),
	DATEPART(qq,@DateValue),
	DATEADD(DAY,1,EOMONTH(@DateValue,-1)),
	EOMONTH(@DateValue),
	DATENAME(mm,@DateValue),
	DATENAME(dw,@DateValue);
END;
GO

EXECUTE dbo.InsertDimDate '2022-12-10';
GO

/* REQUIREMENT 3 */

/* This query returns the top customer orders from the 4th Quarter of 2022 in Canada. It includes the customer's name, city, product, supplier, and the sales person. 
This information can be very important for a business analyst to determine the top total sales that occured in a specific country during a specific time or quarter.
As an example, the business analyst can use this information to further develop business relations and increase customer retention with their top and best customers
through various kinds of deals and business strategies. The business analyst can utulize the product, supplier, and salesperson information to develop
a sales plan while utulizing the salesperson information as they already established a connection with the customer and can be directed to reach out to
the customer.
*/

SELECT DimCustomers.CustomerName, DimCities.CityName, DimProducts.ProductName, DimSuppliers.FullName AS Supplier, DimSalesPeople.FullName AS Salesperson,TotalAfterTax 
FROM FactOrders 
	 INNER JOIN DimCustomers ON FactOrders.CustomerKey = DimCustomers.CustomerKey 
	 INNER JOIN DimCities ON FactOrders.CityKey = DimCities.CityKey
	 INNER JOIN DimProducts ON FactOrders.ProductKey = DimProducts.ProductKey
	 INNER JOIN DimSuppliers ON FactOrders.SupplierKey = DimSuppliers.SupplierKey
	 INNER JOIN DimSalesPeople ON FactOrders.SalespersonKey = DimSalesPeople.SalespersonKey
	 INNER JOIN DimDate ON FactOrders.DateKey = DimDate.DateKey 
WHERE DimDate.Quarter = 4 AND DimDate.Year = 2022 AND CountryName = 'Canada' 
ORDER BY TotalAfterTax DESC;