USE [AWD703515]
GO
/****** Object:  StoredProcedure [dbo].[SP_poblado_Northwind_dim_Star_WTime]    Script Date: 04/09/2019 12:35:29 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_poblado_AWS703515]
AS

DELETE AWD703515.dbo.Facts;
DELETE AWD703515.dbo.Customer;
DELETE AWD703515.dbo.Employee;
DELETE AWD703515.dbo.Products;
DELETE AWD703515.dbo.OrderTable;
DELETE AWD703515.dbo.TIME;


-- customer
INSERT INTO AWD703515.dbo.Customer
SELECT  C.CustomerID,
        P.PersonType AS TYPE,
        P.FirstName,
        P.LastName,
        S.CountryRegionCode
FROM   AdventureWorks2014.Sales.Customer C
JOIN AdventureWorks2014.Person.Person P ON (P.BusinessEntityID = C.PersonID)
JOIN AdventureWorks2014.Sales.SalesTerritory S ON (S.TerritoryID = C.TerritoryID)

-- employee
INSERT INTO AWD703515.dbo.Employee
SELECT  E.BusinessEntityID AS EmployeeID,
        P.FirstName,
        P.LastName,
        E.JobTitle,
        E.Gender
FROM AdventureWorks2014.Sales.SalesPerson SP
JOIN AdventureWorks2014.Person.Person P ON(SP.BusinessEntityID = P.BusinessEntityID)
JOIN AdventureWorks2014.HumanResources.Employee E ON(P.BusinessEntityID = E.BusinessEntityID)
ORDER BY SP.BusinessEntityID


-- order
INSERT INTO AWD703515.dbo.OrderTable
SELECT  SOD.SalesOrderID AS OrderID,
        SUM(SOD.OrderQty) AS OrderQty
FROM AdventureWorks2014.Sales.SalesOrderDetail SOD
GROUP BY SOD.SalesOrderID
ORDER BY SOD.SalesOrderID

-- productos
INSERT INTO AWD703515.dbo.Products
SELECT  P.ProductID,
        P.Name,
        P.ListPrice,
        ISNULL(P.Color, 'No-Color'),
        ISNULL(P.Weight, 0),
        ISNULL(PC.Name, 'No-Category') AS 'CategoryName'
FROM AdventureWorks2014.Production.Product P
LEFT JOIN AdventureWorks2014.Production.ProductSubcategory PS ON(PS.ProductSubcategoryID = P.ProductSubcategoryID)
LEFT JOIN AdventureWorks2014.Production.ProductCategory PC ON(PC.ProductCategoryID = PS.ProductCategoryID)
ORDER BY P.ProductID

-- time
INSERT INTO AWD703515.dbo.TIME
SELECT  DISTINCT CAST(
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),0,5)+
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),6,2)+
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),9,2) AS INT) DATE,
        DATEPART(DD, SOH.OrderDate) AS DAY,
        DATEPART(MM, SOH.OrderDate) AS MONTH,
        DATEPART(YYYY, SOH.OrderDate) AS YEAR,
        DATENAME(DW, SOH.OrderDate) AS Weekday,
        SOH.OrderDate
FROM  AdventureWorks2014.Sales.SalesOrderHeader SOH
ORDER BY DATE

/*-- facts
INSERT INTO AWD703515.dbo.Facts
SELECT  C.CustomerID,
        E.EmployeeID,
        P.ProductID,
        T.TimeID,
        O.OrderID,
        SubTotal,
        TaxAmt,
        TotalDue,
        Freight
FROM    AWD703515.dbo.Customer C,
        AWD703515.dbo.Employee E,
        AWD703515.dbo.Products P,
        AWD703515.dbo.Time T,
        AWD703515.dbo.OrderTable O,
        AdventureWorks2014.Sales.SalesOrderHeader SOH
WHERE   SOH.CustomerID = C.CustomerID
AND     SOH.SalesOrderID = O.OrderID
AND     SOH.OrderDate = T.Date
AND     SalesPersonID = E.EmployeeID*/


--Hechos
INSERT INTO AWD703515.dbo.Facts
SELECT  SOH.CustomerID,
        SOH.SalesPersonID AS EmployeeID,
        SOD.ProductID AS ProductsID,
        T.TimeID,
        SOD.SalesOrderID AS OrderID,
        SOH.SubTotal,
        SOH.TaxAmt,
        SOH.TotalDue,
        SOH.Freight

FROM AdventureWorks2014.Sales.SalesOrderDetail SOD
JOIN  AdventureWorks2014.Sales.SalesOrderHeader SOH ON (SOD.SalesOrderID = SOH.SalesOrderID)
JOIN AWD703515.dbo.TIME T ON (CAST(
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),0,5)+
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),6,2)+
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),9,2) AS INT) ) = T.TimeID
JOIN AWD703515.dbo.Products PDIM ON PDIM.ProductID = SOD.ProductID
JOIN AWD703515.dbo.Customer CDIM ON CDIM.CustomerID = SOH.CustomerID
ORDER BY SOD.SalesOrderID ASC




SELECT  SOH.CustomerID AS CustomerID
FROM AdventureWorks2014.Sales.SalesOrderDetail SOD
JOIN  AdventureWorks2014.Sales.SalesOrderHeader SOH ON (SOD.SalesOrderID = SOH.SalesOrderID)
JOIN AWD703515.dbo.TIME T ON (CAST(
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),0,5)+
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),6,2)+
                SUBSTRING(CONVERT(VARCHAR, SOH.OrderDate, 126),9,2) AS INT) ) = T.TimeID
JOIN AWD703515.dbo.Products PDIM ON PDIM.ProductID = SOD.ProductID
JOIN AWD703515.dbo.Customer CDIM ON CDIM.CustomerID = SOH.CustomerID
WHERE SOH.CustomerID  NOT IN (SELECT CustomerID FROM AWD703515.dbo.Customer)



SELECT * FROM AWD703515.dbo.Customer
SELECT * FROM AdventureWorks2014.Sales.Customer