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'

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

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

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

--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(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 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
  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 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

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