tblfact.sql

来自「< SQL Server2005程序设计>」· SQL 代码 · 共 41 行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblFact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblFact]
GO

CREATE TABLE [dbo].[tblFact] (
	[OrderDate] [datetime] NULL ,
	[RequiredDate] [datetime] NULL ,
	[ShippedDate] [datetime] NULL ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProductID] [int] NOT NULL ,
	[EmployeeId] [int] NULL ,
	[ShipperId] [int] NULL ,
	[ShipRegion] [nvarchar] (15) NULL ,
	[ShipCountry] [nvarchar] (15) NULL ,
	[Total Sales] [money] NULL ,
	[Discount] [float] NULL ,
	[Unit Sales] [int] NULL ,
	[TimeKey] [int] NULL 
) ON [PRIMARY]
GO

Insert Into tblFact 
SELECT Orders.OrderDate, Orders.RequiredDate, ISNULL(Orders.ShippedDate, '01/01/96') AS ShippedDate, 
	Customers.PostalCode,  Products.ProductID, Orders.EmployeeId, Orders.ShipVia AS ShipperId, 
	Orders.ShipRegion, Orders.ShipCountry,
    SUM([Order Details].UnitPrice * [Order Details].Quantity)  AS [Total Sales], 
    SUM([Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount)  AS Discount, 
    SUM([Order Details].Quantity) AS [Unit Sales],
    tblTime.TimeKey
FROM Orders INNER JOIN
    [Order Details] ON 
    Orders.OrderID = [Order Details].OrderID INNER JOIN
    Customers ON 
    Orders.CustomerID = Customers.CustomerID INNER JOIN
    Products ON 
    [Order Details].ProductID = Products.ProductID INNER JOIN
    tblTime ON 
    [Orders].OrderDate = tblTime.OrderDate
GROUP BY Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipRegion, Orders.ShipCountry,
	Customers.PostalCode, Products.ProductID, Orders.EmployeeId, Orders.ShipVia, tblTime.TimeKey