bifacil

----------------
-- Converters --
----------------


[ACCESIBLE CONTENT]
ACTION
CHART

bifacil

;WITH
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)
  WHERE DimGeography.SpanishCountryRegionName='Francia'

bifacil

-- Ejemplo 5
SELECT
  DimDate.CalendarYear AS CalendarYear,
  sum(isales.SalesAmount) AS ventasInternet,
  sum(sales.SalesAmount) AS ventasReseller,
  ventasInternet+ventasReseller VentasTotales
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE DimGeography.SpanishCountryRegionName='Francia'

bifacil

Segundo fichero

bifacil

--Ejemplo 4
SELECT
   DimProduct.EnglishProductName AS EnglishProductName,
   sum(sales.OrderQuantity) WHERE (DimDate.CalendarYear=2008) [Ventas 2008],
   sum(sales.OrderQuantity) WHERE (DimDate.CalendarYear=2007) [Ventas 2007],
   [Ventas 2008]-[Ventas 2007] Diferencia
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE
  DimProductCategory.SpanishProductCategoryName='Bicicleta' 
  AND DimDate.SpanishMonthName='Enero'

bifacil

SELECT
  DimGeography.SpanishCountryRegionName AS SpanishCountryRegionName,
  sum(sales.OrderQuantity) AS Unidades
FROM FactResellerSales sales
INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)
INNER JOIN DimReseller ON (sales.ResellerKey=DimReseller.ResellerKey)
INNER JOIN DimGeography ON (DimReseller.GeographyKey=DimGeography.GeographyKey)
WHERE DimDate.CalendarYear=2008
GROUP BY DimGeography.SpanishCountryRegionName

bifacil

--Ejemplo 3  
SELECT
  DimGeography.SpanishCountryRegionName AS SpanishCountryRegionName,
  sum(sales.OrderQuantity) AS Unidades
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE DimDate.CalendarYear=2008
GROUP BY DimGeography.SpanishCountryRegionName

bifacil

SELECT
  DimProduct.EnglishProductName AS EnglishProductName,
  sum(sales.OrderQuantity) AS Unidades
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)
INNER JOIN DimReseller ON (sales.ResellerKey=DimReseller.ResellerKey)
INNER JOIN DimGeography ON (DimReseller.GeographyKey=DimGeography.GeographyKey)

bifacil

--Ejemplo 2
SELECT
  DimProduct.EnglishProductName AS EnglishProductName,
  sum(sales.OrderQuantity) AS Unidades
FROM DATABASE 'http://bit.ly/2oP72d6'
WHERE
  DimGeography.SpanishCountryRegionName='Alemania'
  AND DimProductCategory.SpanishProductCategoryName='Bicicleta'
  AND DimDate.CalendarYear=2008

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)