USE [AdventureWorksDW]
GO
/****** Object: View [dbo].[vDMPrep] Script Date: 07/29/2008 00:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- vDMPrep will be used as a data source by the other data mining views.
-- Uses DW data at customer, product, day, etc. granularity and
-- gets region, model, year, month, etc.
CREATE VIEW [dbo].[vDMPrep]
AS
SELECT
pc.[EnglishProductCategoryName]
,Coalesce(p.[ModelName], p.[EnglishProductName]) AS [Model]
,c.[CustomerKey]
,s.[SalesTerritoryGroup] AS [Region]
,CASE
WHEN Month(GetDate()) < Month(c.[BirthDate])
THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
WHEN Month(GetDate()) = Month(c.[BirthDate])
AND Day(GetDate()) < Day(c.[BirthDate])
THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
ELSE DateDiff(yy,c.[BirthDate],GetDate())
END AS [Age]
,CASE
WHEN c.[YearlyIncome] < 40000 THEN 'Low'
WHEN c.[YearlyIncome] > 60000 THEN 'High'
ELSE 'Moderate'
END AS [IncomeGroup]
,t.[CalendarYear]
,t.[FiscalYear]
,t.[MonthNumberOfYear] AS [Month]
,f.[SalesOrderNumber] AS [OrderNumber]
,f.SalesOrderLineNumber AS LineNumber
,f.OrderQuantity AS Quantity
,f.ExtendedAmount AS Amount
FROM
[dbo].[FactInternetSales] f
INNER JOIN [dbo].[DimTime] t
ON f.[OrderDateKey] = t.[TimeKey]
INNER JOIN [dbo].[DimProduct] p
ON f.[ProductKey] = p.[ProductKey]
INNER JOIN [dbo].[DimProductSubcategory] psc
ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
INNER JOIN [dbo].[DimProductCategory] pc
ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
INNER JOIN [dbo].[DimCustomer] c
ON f.[CustomerKey] = c.[CustomerKey]
INNER JOIN [dbo].[DimGeography] g
ON c.[GeographyKey] = g.[GeographyKey]
INNER JOIN [dbo].[DimSalesTerritory] s
ON g.[SalesTerritoryKey] = s.[SalesTerritoryKey]
;
