-------------------------------------------------------
-- Range Partitions Exercise 
-- FILE: Script2 - CreateOrders.sql

-- SQL Server 2005
-- Scripts tested on SQL Server 2005
-- 	9.00.1187.07 (June CTP)

-- Partitioned Tables Hands-on Lab Exercises
-- July 2005
-------------------------------------------------------

-------------------------------------------------------
-- Setup Script for Partitioning Exercise

-- This script creates the Orders table with the 
-- same schema as PurchaseOrderHeader.
-- INSERT SELECT is used to populate the table.
-------------------------------------------------------

USE AdventureWorks
GO

-------------------------------------------------------
-- Create the new Orders table.
-------------------------------------------------------

CREATE TABLE AdventureWorks.[dbo].[Orders]
 (
	[OrderID] [int] NOT NULL,
	[EmployeeID] [int] NULL,
	[VendorID] [int] NULL,
	[TaxAmt] [money] NULL,
	[Freight] [money] NULL,
	[SubTotal] [money] NULL,
	[Status] [tinyint] NOT NULL ,
	[RevisionNumber] [tinyint] NULL ,
	[ModifiedDate] [datetime] NULL ,
	[ShipMethodID]	tinyint NULL,
	[ShipDate] [datetime] NOT NULL, 
	[OrderDate] [datetime] NOT NULL, 
	[TotalDue] [money] NULL
)
GO

-------------------------------------------------------
-- Populate with data.
-------------------------------------------------------
INSERT INTO dbo.[Orders]
	SELECT o.[PurchaseOrderID] 
			, o.[EmployeeID]
			, o.[VendorID]
			, o.[TaxAmt]
			, o.[Freight] 
			, o.[SubTotal] 
			, o.[Status] 
			, o.[RevisionNumber] 
			, o.[ModifiedDate] 
			, o.[ShipMethodID] 
			, o.[ShipDate] 
			, o.[OrderDate] 
			, o.[TotalDue] 
	FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
		WHERE ([OrderDate] >= '20030701' 
				 AND [OrderDate] < '20040701')
GO

-------------------------------------------------------
-- Modify Rows to later show RIGHT and LEFT in 
-- RANGE Partitions using DATETIME Data.
-------------------------------------------------------
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20030930'
	WHERE OrderID = 416
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20031001'
	WHERE OrderID = 417
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20031001 10:31am'
	WHERE OrderID = 418
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20031231'
	WHERE OrderID = 1090
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040101'
	WHERE OrderID = 1091
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040101 10:31am'
	WHERE OrderID = 1092
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040331'
	WHERE OrderID = 1860
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040401'
	WHERE OrderID = 1861
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040401 10:31am'
	WHERE OrderID = 1862
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040331'
	WHERE OrderID = 2832
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040401'
	WHERE OrderID = 2833
GO
UPDATE AdventureWorks.[dbo].Orders
	SET OrderDate = '20040401 10:31am'
	WHERE OrderID = 2834
GO
-------------------------------------------------------
-- Optionally, verify row count of data. 
-- 2748 Rows for this date range.
-------------------------------------------------------
SELECT count(*) AS [Orders Row Count]
FROM AdventureWorks.[dbo].Orders
GO