SHARE
TWEET

Untitled

a guest Mar 26th, 2019 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create table Dudek.DIM_CUSTOMER(
  2. CustomerID int not null,
  3. FirstName nvarchar(50) not null,
  4. LastName nvarchar(50) not null,
  5. Title nvarchar(16),
  6. City nvarchar(30) not null,
  7. TerritoryName nvarchar(50) not null,
  8. CountryRegionCode nvarchar(3) not null,
  9. [Group] nvarchar(50) not null
  10. )
  11.  
  12. INSERT INTO Dudek.DIM_CUSTOMER(
  13.     CustomerID,
  14.     FirstName,
  15.     LastName,
  16.     Title,
  17.     City,
  18.     TerritoryName,
  19.     CountryRegionCode,
  20.     [Group]
  21. )
  22. SELECT
  23.     CustomerID,
  24.     FirstName,
  25.     LastName,
  26.     Title,
  27.     City,
  28.     Name,
  29.     CountryRegionCode,
  30.     [Group]
  31. FROM
  32.     AdventureWorks2017.Sales.Customer
  33.     JOIN AdventureWorks2017.Person.Person ON Customer.PersonID = Person.BusinessEntityID
  34.     JOIN AdventureWorks2017.Sales.SalesTerritory ON Customer.TerritoryID = SalesTerritory.TerritoryID
  35.     JOIN AdventureWorks2017.Person.BusinessEntityAddress on Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
  36.     JOIN AdventureWorks2017.Person.Address on BusinessEntityAddress.AddressID = Address.AddressID;
  37.  
  38. create table Dudek.DIM_PRODUCT(
  39. ProductID int not null,
  40. [Name] nvarchar(50) not null,
  41. ListPrice MONEY not null,
  42. Color nvarchar(15) null,
  43. SubCategoryName nvarchar(50) null,
  44. CategoryName nvarchar(50) null,
  45. [Weight] decimal(8,2),
  46. Size nvarchar(5),
  47. )
  48. --DODAC ISPURCHASED
  49. INSERT INTO Dudek.DIM_PRODUCT(
  50.     ProductID,
  51.     Name,
  52.     ListPrice,
  53.     Color,
  54.     SubCategoryName,
  55.     CategoryName,
  56.     Weight,
  57.     Size
  58. )
  59. SELECT
  60.     Product.ProductID,
  61.     Product.Name,
  62.     ListPrice,
  63.     Color,
  64.     ProductSubcategory.Name,
  65.     ProductCategory.Name,
  66.     Weight,
  67.     Size
  68. FROM
  69.     AdventureWorks2017.Production.Product
  70.     LEFT JOIN AdventureWorks2017.Production.ProductSubcategory ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
  71.     LEFT JOIN AdventureWorks2017.Production.ProductCategory ON ProductSubcategory.ProductCategoryID = ProductCategory.ProductCategoryID
  72. GROUP BY Product.ProductID, Product.Name, ListPrice, Color, ProductSubcategory.Name, ProductCategory.Name, Weight, Size;
  73.  
  74.  
  75. create table Dudek.DIM_SALESPERSON(
  76. SalesPersonID int not null,
  77. FirstName nvarchar(50) not null,
  78. LastName nvarchar(50) not null,
  79. Title nvarchar(16),
  80. Gender nchar(1),
  81. CountryRegionCode nvarchar(3),
  82. [Group] nvarchar(50)
  83. )
  84.  
  85. INSERT INTO Dudek.DIM_SALESPERSON(
  86.     SalesPersonID,
  87.     FirstName,
  88.     LastName,
  89.     Title,
  90.     Gender,
  91.     CountryRegionCode,
  92.     [Group]
  93. )
  94. SELECT  
  95.     SalesPerson.BusinessEntityID,
  96.     FirstName,
  97.     LastName,
  98.     Title,
  99.     Gender,
  100.     CountryRegionCode,
  101.     [Group]
  102. FROM
  103. AdventureWorks2017.Sales.SalesPerson JOIN AdventureWorks2017.HumanResources.Employee on SalesPerson.BusinessEntityID = Employee.BusinessEntityID
  104. JOIN AdventureWorks2017.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
  105. LEFT JOIN AdventureWorks2017.Sales.SalesTerritory on SalesPerson.TerritoryID = SalesTerritory.TerritoryID;
  106.  
  107. create table Dudek.FACT_SALES(
  108. ProductID int not null,
  109. CustomerID int not null,
  110. SalesPersonID int,
  111. OrderDate int not null,
  112. ShipDate int,
  113. OrderQty smallint not null,
  114. UnitPrice MONEY not null,
  115. UnitPriceDiscount MONEY not null,
  116. LineTotal as UnitPrice * (1 - UnitPriceDiscount) * OrderQty
  117. );
  118.  
  119. INSERT INTO Dudek.FACT_SALES(
  120.     ProductID,
  121.     CustomerID,
  122.     SalesPersonID,
  123.     OrderDate,
  124.     ShipDate,
  125.     OrderQty,
  126.     UnitPrice,
  127.     UnitPriceDiscount
  128. )
  129. SELECT
  130.     ProductID,
  131.     CustomerID,
  132.     SalesPersonID,
  133.     CAST(
  134.         CAST(DATEPART(YEAR, OrderDate) AS nvarchar) +
  135.         (CASE
  136.             WHEN DATEPART(MONTH, OrderDate) < 10 THEN '0' + CAST(DATEPART(MONTH, OrderDate) AS nvarchar)
  137.             ELSE CAST(DATEPART(MONTH, OrderDate) AS nvarchar)
  138.         END) +
  139.         (CASE
  140.             WHEN DATEPART(DAY, OrderDate) < 10 THEN '0' + CAST(DATEPART(DAY, OrderDate) AS nvarchar)
  141.             ELSE CAST(DATEPART(DAY, OrderDate) AS nvarchar)
  142.         END) AS int
  143.     ),
  144.     CAST(
  145.         CAST(DATEPART(YYYY, ShipDate) AS nvarchar) +
  146.         (CASE
  147.             WHEN DATEPART(MONTH, ShipDate) < 10 THEN '0' + CAST(DATEPART(MONTH, ShipDate) AS nvarchar)
  148.             ELSE CAST(DATEPART(MONTH, ShipDate) AS nvarchar)
  149.         END) +
  150.         (CASE
  151.             WHEN DATEPART(DAY, ShipDate) < 10 THEN '0' + CAST(DATEPART(DAY, ShipDate) AS nvarchar)
  152.             ELSE CAST(DATEPART(DAY, ShipDate) AS nvarchar)
  153.         END) AS int
  154.     ),
  155.     OrderQty,
  156.     UnitPrice,
  157.     UnitPriceDiscount
  158. FROM
  159.     AdventureWorks2017.Sales.SalesOrderHeader
  160.     JOIN AdventureWorks2017.Sales.SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top