--© 2018 | ByrdNest Consulting

--now load ProductDim (Initial data load)
USE AdventureWorksDW2012lite
GO

INSERT INTO dbo.ProductDim
           (ProductAlternateKey,ProductSubcategoryKey,WeightUnitMeasureCode,SizeUnitMeasureCode,EnglishProductName
           ,SpainishProductName,FrenchProductName,StandardCost,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint
           ,ListPrice,Size,SizeRange,[Weight],DaysToManufacture,ProductLine,DealerPrice,Class,Style,ModelName
           ,LargePhoto,EnglishDescription,FrenchDescription,ChineseDescription,ArabicDescription,HebrewDescription
           ,ThaiDescription,SpainishDescription,JapaneseDescription,TurkishDescription,StartDate,EndDate,[Status]
		   ,SrcProductID, SrcProductSubcategoryID)
    SELECT	ProductNumber ProductAlternateKey,
			ISNULL(scd.ProductSubcategoryKey,-1) ProductSubcategoryKey,
			p.WeightUnitMeasureCode,
			p.SizeUnitMeasureCode,
			p.Name EnglishProductName,
			'' SpanishProductName,
			'' FrenchProductName,
			p.StandardCost,
			p.FinishedGoodsFlag,
			ISNULL(p.Color,''),
			p.SafetyStockLevel,
			p.ReorderPoint,
			p.ListPrice,
			p.Size,
			NULL SizeRange,
			p.[Weight],
			p.DaysToManufacture,
			p.ProductLine,
			NULL DealerPrice,
			p.Class,
			p.Style,
			pm.Name ModelName,
			pp.LargePhoto,
			sub.EnglishDescription,
			sub.FrenchDescription,
			sub.ChineseDescription,
			sub.ArabicDescription,
			sub.HebrewDescription,
			sub.ThaiDescription,
			sub.SpainishDescription,
			NULL JapaneseDescription,
			NULl TurkishDescription,
			p.SellStartDate StartDate,
			p.SellEndDate EndDate,
			NULL [Status],
			p.ProductID SrcProductID,
			p.ProductSubcategoryID
		FROM AdventureWorks2012.Production.Product p
		JOIN AdventureWorks2012.Production.ProductModel pm
		  ON pm.ProductModelID = p.ProductModelID
		LEFT JOIN (SELECT t.ProductModelID,
					MAX(CASE WHEN	t.CultureID = 'en' THEN t.ProductDescription ELSE NULL END) EnglishDescription,
					MAX(CASE WHEN	t.CultureID = 'fr' THEN t.ProductDescription ELSE NULL END) FrenchDescription,
					MAX(CASE WHEN	t.CultureID = 'zh-cht' THEN t.ProductDescription ELSE NULL END) ChineseDescription,
					MAX(CASE WHEN	t.CultureID = 'ar' THEN t.ProductDescription ELSE NULL END) ArabicDescription,
					MAX(CASE WHEN	t.CultureID = 'he' THEN t.ProductDescription ELSE NULL END) HebrewDescription,
					MAX(CASE WHEN	t.CultureID = 'th' THEN t.ProductDescription ELSE NULL END) ThaiDescription,
					MAX(CASE WHEN	t.CultureID = 'es' THEN t.ProductDescription ELSE NULL END) SpainishDescription
			FROM (select pmpdc.ProductModelID,pmpdc.CultureID,pd.[Description] ProductDescription
			FROM AdventureWorks2012.Production.ProductModelProductDescriptionCulture pmpdc
			JOIN AdventureWorks2012.Production.ProductDescription pd
			  ON pd.ProductDescriptionID = pmpdc.ProductDescriptionID) t
			GROUP BY t.ProductModelID) sub
			  ON sub.ProductModelID = pm.ProductModelID
		LEFT JOIN AdventureWorks2012.Production.ProductProductPhoto ppp
		  ON ppp.ProductID = p.ProductID
		LEFT JOIN AdventureWorks2012.Production.ProductPhoto pp
		  ON pp.ProductPhotoID = ppp.ProductPhotoID
		LEFT JOIN AdventureWorks2012.[Production].[ProductSubcategory] psc
		  ON psc.ProductSubcategoryID = p.ProductSubcategoryID
		LEFT JOIN [dbo].[ProductSubCategoryDim] scd
		  ON scd.EnglishProductSubcategoryName = psc.Name
GO

--now load CustomerDim except for CustomerID = 11566 (25 internet sales orders)
IF OBJECT_ID(N'tempdb..#Demographics') IS NOT NULL DROP TABLE #Demographics
GO
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
SELECT BusinessEntityID PersonID,
   C.value('ns:BirthDate[1]','Date') AS BirthDate,
   C.value('ns:MaritalStatus[1]','nchar(1)') AS MaritalStatus,
   C.value('ns:Gender[1]','nchar(1)') AS Gender,
   C.value('ns:YearlyIncome[1]','VARCHAR(50)') AS YearlyIncome,
   C.value('ns:TotalChildren[1]','tinyint') AS TotalChildren,
   C.value('ns:NumberChildrenAtHome[1]','tinyint') AS NumberChildrenAtHome,
   C.value('ns:Education[1]','nvarchar(40)') AS Education,
   C.value('ns:Occupation[1]','nvarchar(100)') AS EnglishOccupation,
   C.value('ns:HomeOwnerFlag[1]','nchar(1)') AS HomeOwnerFlag,
   C.value('ns:NumberCarsOwned[1]','tinyint') AS NumberCarsOwned,
   C.value('ns:DateFirstPurchase[1]','date') AS DateFirstPurchase,
   C.value('ns:CommuteDistance[1]','varchar(50)') AS CommuteDistance
INTO #Demographics
FROM AdventureWorks2012.Person.Person
CROSS APPLY Demographics.nodes('/ns:IndividualSurvey') AS T(C)


INSERT INTO dbo.CustomerDim
           (GeographyKey,CustomerAlternateKey,Title,FirstName,MiddleName
           ,LastName,NameStyle,BirthDate,MaritalStatus,Suffix,Gender,EmailAddress
           ,YearlyIncome,TotalChildren,NumberChildrenAtHome,EnglishEducation
           ,SpanishEducation,FrenchEducation,EnglishOccupation,SpanishOccupation
           ,FrenchOccupation,HouseOwnerFlag,NumberCarsOwned,AddressLine1,AddressLine2
           ,Phone,DateFirstPurchase,CommuteDistance,SrcCustomerID,SrcPersonID)
	SELECT	DISTINCT gd.GeographyKey,
			c.AccountNumber CustomerAlternateKey,
			p.Title,
			p.FirstName,
			p.MiddleName,
			p.LastName,
			p.NameStyle,
			x.BirthDate,
			x.MaritalStatus,
			p.Suffix,
			x.Gender,
			ea.EmailAddress,
			x.YearlyIncome,
			x.TotalChildren,
			x.NumberChildrenAtHome,
			x.Education EnglishEducation,
			NULL SpainishEducation,
			NULL FrenchEducation,
			x.EnglishOccupation,
			NULL SpainishOccupation,
			NULL FrenchOccupation,
			x.HomeOwnerFlag,
			x.NumberCarsOwned,
			a.AddressLine1,
			a.AddressLine2,
			pp.PhoneNumber Phone,
			x.DateFirstPurchase,
			x.CommuteDistance,
			c.CustomerID SrcCustomerID,
			c.PersonID SrcPersonID
		FROM AdventureWorks2012.Sales.Customer c
		JOIN AdventureWorks2012.Person.Person p
		  ON p.BusinessEntityID = c.PersonID
		JOIN AdventureWorks2012.[Person].[BusinessEntityAddress] bea
		  ON bea.BusinessEntityID = p.BusinessEntityID
		JOIN AdventureWorks2012.Person.[Address] a
		  ON a.AddressID = bea.AddressID
		JOIN AdventureWorks2012.Person.PersonPhone pp
		  on pp.BusinessEntityID = p.BusinessEntityID
		JOIN AdventureWorks2012.Sales.SalesTerritory t
		  ON t.TerritoryID = c.territoryID
		JOIN #Demographics x
		  on x.PersonID = c.PersonID
		JOIN AdventureWorks2012.Person.StateProvince sp
		  ON sp.StateProvinceID = a.StateProvinceID
		JOIN AdventureWorks2012.Person.EmailAddress ea
		  ON ea.BusinessEntityID = p.BusinessEntityID
		JOIN dbo.GeographyDim gd
		  ON  gd.City				= a.city
		  AND gd.StateProvinceCode	= sp.StateProvinceCode
		  AND gd.CountryRegionCode	= sp.CountryRegionCode
		  AND gd.PostalCode			= a.PostalCode
		WHERE customerID <> 11566
		  AND bea.AddressTypeID = 2 --home
--(18483 row(s) affected)
GO

--now load InternetSalesFact table (except for customerID = 11566)
INSERT INTO dbo.InternetSalesFact
           (ProductKey,OrderDateKey,DueDateKey,ShipDateKey,CustomerKey,PromotionKey,CurrencyKey
           ,SalesTerritoryKey,SalesOrderNumber,SalesOrderLineNumber,RevisionNumber,OrderQuantity
           ,UnitPrice,ExtendedAmount,UnitPriceDiscountPct,DiscountAmount,ProductStandardCost
           ,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber
           ,OrderDate,DueDate,ShipDate,SrcProductID,SrcCustomerId)
		SELECT	DISTINCT ISNULL(prod.ProductKey,-1) ProductKey,
				CONVERT(INT,CONVERT(VARCHAR(25),soh.OrderDate,112)) OrderDateKey,
				CONVERT(INT,CONVERT(VARCHAR(25),soh.DueDate,112)) DueDateKey,
				CONVERT(INT,CONVERT(VARCHAR(25),soh.ShipDate,112)) ShipDateKey,
				ISNULL(cd.CustomerKey,-1) CustomerKey,
				ISNULL(pd.PromotionKey,-1) PromotionKey,
				ISNULL(curr.CurrencyKey,-1) CurrencyKey,
				ISNULL(std.SalesTerritoryKey,-1) SalesTerritoryKey,
				soh.SalesOrderNumber,
				Row_Number() OVER (Partition By sod.SalesOrderID ORDER BY sod.SalesOrderDetailID) SalesOrderLineNumber,
				soh.RevisionNumber,
				sod.OrderQty OrderQuantity,
				sod.UnitPrice,
				0 ExtendedAmount,
				sod.UnitPriceDiscount*100/sod.UnitPrice UnitPriceDiscountPct,
				sod.UnitPriceDiscount DiscountAmount,
				0 ProductStandardCost,
				sod.LineTotal TotalProductCost,
				soh.SubTotal SalesAmount,
				soh.TaxAmt,
				soh.Freight,
				sod.CarrierTrackingNumber CarrierTrackingNumber,
				soh.PurchaseOrderNumber CustomerPONumber,
				soh.OrderDate,
				soh.DueDate,
				soh.ShipDate,
				sod.productID SrcProductID,
				soh.CustomerID SrcCustomerId
			FROM AdventureWorks2012.Sales.SalesOrderHeader soh
			JOIN AdventureWorks2012.Sales.SalesOrderDetail sod
			  ON sod.SalesOrderID = soh.SalesOrderID
			JOIN AdventureWorks2012.Sales.CurrencyRate cr
			  ON cr.CurrencyRateID = soh.currencyRateID
			JOIN AdventureWorks2012.Sales.SpecialOffer so
			  ON so.SpecialOfferID = sod.SpecialOfferID
			LEFT JOIN dbo.PromotionDim pd
			  ON  pd.EnglishPromotionName = so.[Description]
			  AND pd.DiscountPct = so.DiscountPct
			  AND pd.EnglishPromotionType = so.[Type]
			LEFT JOIN dbo.CustomerDim cd
			  ON cd.SrcCustomerID = soh.CustomerID
			LEFT JOIN dbo.ProductDim prod
			  ON prod.SrcProductID = sod.ProductID
			LEFT JOIN dbo.CurrencyDim curr
			  ON curr.CurrencyAlternateKey = cr.ToCurrencyCode
			LEFT JOIN dbo.SalesTerritoryDim std
			  on std.SrcSalesTerritoryID = soh.TerritoryID
			WHERE OnlineOrderFlag = 1
			  AND soh.CustomerID <> 11566;
GO


--completed initial data load, do admin
DECLARE @Date	DATETIME = GETUTCDATE();
INSERT etl.[ETL]
    (StartDate,EndDate, CTCompleteFlag, ExtractCompleteFlag, LoadCompleteFlag, PreviousChangeVersion, CurrentChangeVersion) -- Set Change Versions
	VALUES (@DATE, @DATE,'Y', 'Y', 'Y', 0, 0)
GO