tblfact.sql

< SQL Server2005程序设计>

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 ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProductID] [int] NOT NULL ,
	[EmployeeId] [int] NULL ,
	[ShipperId] [int] NULL ,
	[Total Sales] [money] NULL ,
	[Discount] [float] NULL ,
	[Unit Sales] [int] NULL ,
	[TimeKey] [int] NULL 
) ON [PRIMARY]
GO


Insert Into tblFact 
SELECT Orders.OrderDate, Customers.PostalCode,  Products.ProductID, Orders.EmployeeId, Orders.ShipVia AS ShipperId, 
    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, Customers.PostalCode, Products.ProductID, Orders.EmployeeId, Orders.ShipVia, tblTime.TimeKey