bifacil

WITH
query AS (
  SELECT
    ProductID,
    Product.Name AS Product,
    ProductCategory.name AS ProductCategory,
    ProductSubCategory.name AS ProductSubCategory,
    ProductNumber,
    ProductModel.name AS ProductModel,
    Color,

bifacil

-- Ejemplo 1
SELECT
  DimProduct.EnglishProductName AS Producto,
  sum(sales.OrderQuantity) AS Unidades,
  sum(sales.SalesAmount) AS Importe,
  Importe/Unidades AS [Precio Medio]
FROM FactResellerSales sales
INNER JOIN DimProduct USING ProductKey
INNER JOIN DimProductSubCategory USING DimProduct(ProductSubcategoryKey)
INNER JOIN DimProductCategory USING DimProductSubCategory(ProductCategoryKey)

bifacil

SELECT
  DimProduct.EnglishProductName AS Producto,
  sum(sales.OrderQuantity) AS Unidades,
  sum(sales.SalesAmount) AS Importe,
  sum(sales.SalesAmount)/sum(sales.OrderQuantity) AS [Precio Medio]
FROM FactResellerSales sales
INNER JOIN DimProduct ON (sales.ProductKey=DimProduct.ProductKey)
INNER JOIN DimProductSubCategory ON (DimProduct.ProductSubcategoryKey=DimProductSubCategory.ProductSubcategoryKey)
INNER JOIN DimProductCategory ON (DimProductSubCategory.ProductCategoryKey=DimProductCategory.ProductCategoryKey)
INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)

bifacil

-- Ejemplo 4
COMBINE  BY EnglishProductName
  sales1 AS (
    SELECT
      DimProduct.EnglishProductName AS EnglishProductName,
      sum(sales.OrderQuantity) AS Quantity2007
    FROM FactResellerSales sales
    INNER JOIN DimProduct USING ProductKey
    INNER JOIN DimProductSubCategory USING DimProduct(ProductSubcategoryKey)
    INNER JOIN DimProductCategory USING DimProductSubCategory(ProductCategoryKey)

bifacil

SELECT
  coalesce(sales1.EnglishProductName,sales2.EnglishProductName) EnglishProductName,
  sales1.Quantity2007 Quantity2007,
  sales2.Quantity2008 Quantity2008
FROM 
    (SELECT
      DimProduct.EnglishProductName AS EnglishProductName,
      sum(sales.OrderQuantity) AS Quantity2007
    FROM FactResellerSales sales
    INNER JOIN DimProduct ON (sales.ProductKey=DimProduct.ProductKey)

bifacil

--Ejemplo 5 
COMBINE BY CalendarYear
  isales1 AS (
    SELECT
      DimDate.CalendarYear AS CalendarYear,
      sum(isales.SalesAmount) AS ventasInternet
    FROM FactInternetSales isales
    INNER JOIN DimDate ON (isales.OrderDateKey=DimDate.DateKey)
    INNER JOIN DimCustomer ON (isales.CustomerKey=DimCustomer.CustomerKey)
    INNER JOIN DimGeography ON (DimCustomer.GeographyKey=DimGeography.GeographyKey)

bifacil

SELECT
  coalesce(isales1.CalendarYear,sales1.CalendarYear) CalendarYear,
  isales1.ventasInternet ventasInternet,
  sales1.ventasReseller ventasReseller
FROM 
    (SELECT
      DimDate.CalendarYear AS CalendarYear,
      sum(isales.SalesAmount) AS ventasInternet
    FROM FactInternetSales isales
    INNER JOIN DimDate ON (isales.OrderDateKey=DimDate.DateKey)

bifacil

MERGE dwh.DimProducts WHEN MISSING THEN SET FechaBaja=getdate(), Deleted=1
select 
  ProductID #ProductId,
  Product.Name Product,
  ProductCategory.name ProductCategory,
  ProductSubCategory.name ProductSubCategory,
  ProductNumber,
  ProductModel.name ProductModel,
  Product.Color,
  Product.StandardCost ProductCost,

bifacil

;WITH
query AS (
  SELECT
    ProductID AS ProductId,
    Product.Name AS Product,
    ProductCategory.name AS ProductCategory,
    ProductSubCategory.name AS ProductSubCategory,
    ProductNumber,
    ProductModel.name AS ProductModel,
    Product.Color AS Color,

bifacil

--Ejemplo 1
SELECT
  DimProduct.EnglishProductName AS Producto,
  sum(sales.OrderQuantity) AS Unidades,
  sum(sales.SalesAmount) AS Importe,
  Importe/Unidades AS [Precio Medio]
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE
  DimProductCategory.SpanishProductCategoryName='Bicicleta'
  AND DimDate.SpanishMonthName='Enero'