Skip to content

Instantly share code, notes, and snippets.

View bifacil's full-sized avatar
😃

Pau Urquizu bifacil

😃
  • Crono Business Intelligence
  • Barcelona
View GitHub Profile
----------------
-- Converters --
----------------
[ACCESIBLE CONTENT]
ACTION
CHART
;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'
-- 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'
Segundo fichero
--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'
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
--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
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)
--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
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)