--Cleaned DimDate Table--
SELECT Datekey,
       FullDateAlternateKey AS DATE,
       EnglishDayNameOfWeek AS Day,
       EnglishMonthName AS Month,
       LEFT(EnglishMonthName, 3) AS Month_abv,
       MonthNumberOfYear AS Month_Num,
       CalendarQuarter AS Quarter,
       CalendarYear AS Year
FROM dbo.DimDate
WHERE CalendarYear >= 2019;

--Cleaned DimCustomer Table--
SELECT  
	c.CustomerKey AS CustomerKey,
	c.Firstname AS [First Name],
	c.Lastname AS [Last Name],
	c.FirstName + ' ' + c.LastName AS [Full Name],
	CASE c.gender
		WHEN 'm' THEN 'Male'
		WHEN 'f' THEN 'Female'
	END AS Gender,
	c.DateFirstPurchase AS First_Purchase_Date,
	g.city AS [Customer City]
FROM DimCustomer c
LEFT JOIN DimGeography g -- Joined city from Geography Table  
	ON c.GeographyKey = g.geographykey
ORDER BY c.customerkey;

--Cleaned DimProduct Table--
SELECT 
	p.productkey,
	p.ProductAlternateKey AS ProductCode,
	p.EnglishProductName AS ProductName,
	ps.EnglishProductSubcategoryName AS [Sub Category],
	pc.EnglishProductCategoryName AS [Product Category],
	p.color AS [Product Color],
	p.size AS [Product Size],
	p.ProductLine AS [Product Line],
	p.ModelName AS [Product Model Name],
	p.EnglishDescription AS [Product Description],
	ISNULL (p.Status, 'Outdated') AS [Product Status]
FROM 
	DimProduct p
LEFT JOIN DimProductCategory pc
	ON p.EnglishProductName = pc.EnglishProductCategoryName
LEFT JOIN DimProductSubcategory ps
	ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
ORDER BY p.productkey;

--Cleaned FactInternetSales Table--
SELECT 
	ProductKey,
	OrderDateKey,
	DueDateKey,
	ShipDateKey,
	CustomerKey,
	SalesOrderNumber,
	SalesAmount
FROM FactInternetSales
WHERE 
	LEFT (OrderDateKey, 4) >= YEAR(GETDATE()) -3 -- Ensures only previous two years of data;