-- ===============================================
-- CONFIG ASP.NET Reports STARTER KIT DATABASE
-- Create Database Script
--
-- Version: 1.2 - 10/02 (mho)
--
-- ===============================================
USE master
CREATE DATABASE [Reports]
exec sp_dboption N'Reports', N'autoclose', N'false'
exec sp_dboption N'Reports', N'bulkcopy', N'true'
exec sp_dboption N'Reports', N'trunc. log', N'true'
exec sp_dboption N'Reports', N'torn page detection', N'true'
exec sp_dboption N'Reports', N'read only', N'false'
exec sp_dboption N'Reports', N'dbo use', N'false'
exec sp_dboption N'Reports', N'single', N'false'
exec sp_dboption N'Reports', N'autoshrink', N'false'
exec sp_dboption N'Reports', N'ANSI null default', N'false'
exec sp_dboption N'Reports', N'recursive triggers', N'false'
exec sp_dboption N'Reports', N'ANSI nulls', N'false'
exec sp_dboption N'Reports', N'concat null yields null', N'false'
exec sp_dboption N'Reports', N'cursor close on commit', N'false'
exec sp_dboption N'Reports', N'default to local cursor', N'false'
exec sp_dboption N'Reports', N'quoted identifier', N'false'
exec sp_dboption N'Reports', N'ANSI warnings', N'false'
exec sp_dboption N'Reports', N'auto create statistics', N'true'
exec sp_dboption N'Reports', N'auto update statistics', N'true'
GO
use [Reports]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Reports_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Products] DROP CONSTRAINT FK_Reports_Products_Categories
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Reports_Orders_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Orders] DROP CONSTRAINT FK_Reports_Orders_Customers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Employees_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Employees] DROP CONSTRAINT FK_Employees_Employees
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Employees
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Orders] DROP CONSTRAINT FK_Orders_Employees
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Territories_Region]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Territories] DROP CONSTRAINT FK_Territories_Region
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Shippers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Orders] DROP CONSTRAINT FK_Orders_Shippers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Products] DROP CONSTRAINT FK_Products_Suppliers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Order Details] DROP CONSTRAINT FK_Order_Details_Orders
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_Order Details] DROP CONSTRAINT FK_Order_Details_Products
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Territories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reports_EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Territories
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetCategorySales]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetCategorySales]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetEmployeeSalesByTerritory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetEmployeeSalesByTerritory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetOrderDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetOrderDetails]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetOrderSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetOrderSummary]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetOrdersAndDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetOrdersAndDetails]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetSalesByRegion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetSalesByRegion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetSalesByTerritory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetSalesByTerritory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetCategories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetOrders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetOrders]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetProductsByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetProductsByCategory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetAllCustomers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetAllCustomers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetCustomerContacts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetCustomerContacts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetEmployeeByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetEmployeeByID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_GetEmployees]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Reports_GetEmployees]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_EmployeeTerritories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_EmployeeTerritories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Order Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Order Details]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_CustomerCustomerDemo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Orders]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Products]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Territories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Territories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_CustomerDemographics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_CustomerDemographics]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Customers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Employees]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Region]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Region]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Shippers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Shippers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reports_Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reports_Suppliers]
GO
CREATE TABLE [dbo].[Reports_Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_CustomerDemographics] (
[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Customers] (
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Employees] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BirthDate] [datetime] NULL ,
[HireDate] [datetime] NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Photo] [image] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReportsTo] [int] NULL ,
[PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Region] (
[RegionID] [int] NOT NULL ,
[RegionDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Shippers] (
[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Suppliers] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_CustomerCustomerDemo] (
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL ,
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TerritoryID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SupplierID] [int] NULL ,
[CategoryID] [int] NULL ,
[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitPrice] [money] NULL ,
[UnitsInStock] [smallint] NULL ,
[UnitsOnOrder] [smallint] NULL ,
[ReorderLevel] [smallint] NULL ,
[Discontinued] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Territories] (
[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerritoryDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RegionID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_EmployeeTerritories] (
[EmployeeID] [int] NOT NULL ,
[TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports_Order Details] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Quantity] [smallint] NOT NULL ,
[Discount] [real] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Categories] WITH NOCHECK ADD
CONSTRAINT [Reports_Reports_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Customers] WITH NOCHECK ADD
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Employees] WITH NOCHECK ADD
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Shippers] WITH NOCHECK ADD
CONSTRAINT [PK_Shippers] PRIMARY KEY CLUSTERED
(
[ShipperID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Suppliers] WITH NOCHECK ADD
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
(
[SupplierID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Products] WITH NOCHECK ADD
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Order Details] WITH NOCHECK ADD
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
(
[OrderID],
[ProductID]
) ON [PRIMARY]
GO
CREATE INDEX [CategoryName] ON [dbo].[Reports_Categories]([CategoryName]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_CustomerDemographics] ADD
CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED
(
[CustomerTypeID]
) ON [PRIMARY]
GO
CREATE INDEX [City] ON [dbo].[Reports_Customers]([City]) ON [PRIMARY]
GO
CREATE INDEX [CompanyName] ON [dbo].[Reports_Customers]([CompanyName]) ON [PRIMARY]
GO
CREATE INDEX [PostalCode] ON [dbo].[Reports_Customers]([PostalCode]) ON [PRIMARY]
GO
CREATE INDEX [Region] ON [dbo].[Reports_Customers]([Region]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Employees] ADD
CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
GO
CREATE INDEX [LastName] ON [dbo].[Reports_Employees]([LastName]) ON [PRIMARY]
GO
CREATE INDEX [PostalCode] ON [dbo].[Reports_Employees]([PostalCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Region] ADD
CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED
(
[RegionID]
) ON [PRIMARY]
GO
CREATE INDEX [CompanyName] ON [dbo].[Reports_Suppliers]([CompanyName]) ON [PRIMARY]
GO
CREATE INDEX [PostalCode] ON [dbo].[Reports_Suppliers]([PostalCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_CustomerCustomerDemo] ADD
CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY NONCLUSTERED
(
[CustomerID],
[CustomerTypeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Orders] ADD
CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
GO
CREATE INDEX [CustomerID] ON [dbo].[Reports_Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [CustomersOrders] ON [dbo].[Reports_Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeeID] ON [dbo].[Reports_Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeesOrders] ON [dbo].[Reports_Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [OrderDate] ON [dbo].[Reports_Orders]([OrderDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippedDate] ON [dbo].[Reports_Orders]([ShippedDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippersOrders] ON [dbo].[Reports_Orders]([ShipVia]) ON [PRIMARY]
GO
CREATE INDEX [ShipPostalCode] ON [dbo].[Reports_Orders]([ShipPostalCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Products] ADD
CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0) FOR [UnitsInStock],
CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0) FOR [UnitsOnOrder],
CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0) FOR [ReorderLevel],
CONSTRAINT [DF_Products_Discontinued] DEFAULT (0) FOR [Discontinued],
CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),
CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)
GO
CREATE INDEX [CategoriesProducts] ON [dbo].[Reports_Products]([CategoryID]) ON [PRIMARY]
GO
CREATE INDEX [CategoryID] ON [dbo].[Reports_Products]([CategoryID]) ON [PRIMARY]
GO
CREATE INDEX [ProductName] ON [dbo].[Reports_Products]([ProductName]) ON [PRIMARY]
GO
CREATE INDEX [SupplierID] ON [dbo].[Reports_Products]([SupplierID]) ON [PRIMARY]
GO
CREATE INDEX [SuppliersProducts] ON [dbo].[Reports_Products]([SupplierID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Territories] ADD
CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED
(
[TerritoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_EmployeeTerritories] ADD
CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY NONCLUSTERED
(
[EmployeeID],
[TerritoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Order Details] ADD
CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],
CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
GO
CREATE INDEX [OrderID] ON [dbo].[Reports_Order Details]([OrderID]) ON [PRIMARY]
GO
CREATE INDEX [OrdersOrder_Details] ON [dbo].[Reports_Order Details]([OrderID]) ON [PRIMARY]
GO
CREATE INDEX [ProductID] ON [dbo].[Reports_Order Details]([ProductID]) ON [PRIMARY]
GO
CREATE INDEX [ProductsOrder_Details] ON [dbo].[Reports_Order Details]([ProductID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Reports_Employees] ADD
CONSTRAINT [FK_Employees_Employees] FOREIGN KEY
(
[ReportsTo]
) REFERENCES [dbo].[Reports_Employees] (
[EmployeeID]
)
GO
ALTER TABLE [dbo].[Reports_CustomerCustomerDemo] ADD
CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY
(
[CustomerTypeID]
) REFERENCES [dbo].[Reports_CustomerDemographics] (
[CustomerTypeID]
),
CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Reports_Customers] (
[CustomerID]
)
GO
ALTER TABLE [dbo].[Reports_Orders] ADD
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Reports_Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [dbo].[Reports_Shippers] (
[ShipperID]
),
CONSTRAINT [FK_Reports_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Reports_Customers] (
[CustomerID]
)
GO
ALTER TABLE [dbo].[Reports_Products] ADD
CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
(
[SupplierID]
) REFERENCES [dbo].[Reports_Suppliers] (
[SupplierID]
),
CONSTRAINT [FK_Reports_Products_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Reports_Categories] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[Reports_Territories] ADD
CONSTRAINT [FK_Territories_Region] FOREIGN KEY
(
[RegionID]
) REFERENCES [dbo].[Reports_Region] (
[RegionID]
)
GO
ALTER TABLE [dbo].[Reports_EmployeeTerritories] ADD
CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Reports_Employees] (
[EmployeeID]
),
CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
(
[TerritoryID]
) REFERENCES [dbo].[Reports_Territories] (
[TerritoryID]
)
GO
ALTER TABLE [dbo].[Reports_Order Details] ADD
CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Reports_Orders] (
[OrderID]
),
CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Reports_Products] (
[ProductID]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetAllCustomers
AS
SELECT * FROM Reports_Customers
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetCustomerContacts
AS
SELECT CompanyName, ContactName, ContactTitle, Phone, City
FROM Reports_Customers
WHERE Phone IS NOT NULL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetEmployeeByID
(
@EmployeeID int
)
AS
SELECT e.EmployeeID,
e.FirstName + ' ' + e.LastName as EmployeeName,
e.LastName,
e.FirstName,
e.Title,
e.HireDate,
e.Address,
e.City,
e.Region,
e.PostalCode,
e.Country,
e.HomePhone,
e.Extension,
e2.FirstName + ' ' + e2.LastName as ReportsTo
FROM Reports_Employees e
INNER JOIN Reports_Employees e2 ON
e.ReportsTo = e2.EmployeeID
WHERE e.EmployeeID = @EmployeeID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetEmployees
AS
SELECT
e.FirstName + ' ' + e.LastName as EmployeeName,
e.Notes as EmployeeNotes,
e.Title as EmployeeTitle,
e.Extension as EmployeeExt,
e.Address as EmployeeAddress,
e.City as EmployeeCity,
e.Region as EmployeeState,
e.PostalCode as EmployeeZip
FROM
Reports_Employees e
WHERE
e.Notes IS NOT NULL
ORDER BY e.LastName, e.FirstName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure Reports_GetCategories
AS
SELECT
Reports_Categories.CategoryID,
Reports_Categories.CategoryName,
Sum(Reports_Products.UnitsInStock) AS TotalInStock
FROM
Reports_Products
INNER JOIN Reports_Categories
ON Reports_Products.CategoryID = Reports_Categories.CategoryID
GROUP BY Reports_Categories.CategoryID, Reports_Categories.CategoryName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetOrders
(
@CustomerID varchar(5)
)
AS
SELECT * FROM Reports_Orders WHERE CustomerID = @CustomerID AND ShippedDate <> ''
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetProductsByCategory
(
@CategoryID int
)
AS
SELECT
CategoryID,
ProductName,
QuantityPerUnit,
UnitPrice,
UnitsInStock
FROM
Reports_Products
WHERE
CategoryID = @CategoryID
ORDER BY
ProductName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetCategorySales
AS
SELECT
C.CategoryName,
Sum(OD.UnitPrice * OD.Quantity * (1-OD.Discount)) Sales
FROM
[Reports_Order Details] OD
INNER JOIN Reports_Products P
ON OD.ProductID = P.ProductID
INNER JOIN Reports_Categories C
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName
ORDER BY C.CategoryName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetEmployeeSalesByTerritory
(
@TerritoryName nchar(50),
@Year int = 1996
)
AS
SELECT e.EmployeeID,
e.FirstName + ' ' + e.LastName as EmployeeName,
SUM(d.UnitPrice * d.Quantity) as 'SalesTotals'
FROM Reports_Orders o
INNER JOIN [Reports_Order Details] d
ON o.OrderID = d.OrderID
INNER JOIN Reports_Employees e
ON o.EmployeeID = e.EmployeeID
INNER JOIN Reports_Territories t
ON o.TerritoryID = t.TerritoryID
WHERE
t.TerritoryDescription = RTRIM(@TerritoryName)
AND DATEPART(yy, o.OrderDate) = @Year
GROUP BY e.EmployeeID, e.LastName, e.FirstName
ORDER BY e.LastName, e.FirstName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetOrderDetails
(
@orderID int
)
AS
Select
od.ProductID, od.UnitPrice, Quantity, ProductName
FROM
[Reports_Order Details] od
JOIN
Reports_Products p
ON od.ProductID = p.ProductID
WHERE OrderID = @orderID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetOrderSummary
(
@Year int
)
AS
SELECT
DATEPART(q, OrderDate) Quarter,
COUNT(DISTINCT Reports_Orders.OrderID) OrdersShipped,
SUM(UnitPrice * Quantity * (1-Discount)) Sales
FROM Reports_Orders
INNER JOIN [Reports_Order Details] Details
ON Reports_Orders.OrderID = Details.OrderID
WHERE YEAR(OrderDate) = @Year
GROUP BY DATEPART(q, OrderDate)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetOrdersAndDetails
(
@Year int,
@Quarter int
)
AS
SELECT
Reports_Orders.OrderID,
OrderDate,
SUM(UnitPrice * Quantity * (1-Discount)) Sales
FROM Reports_Orders
INNER JOIN [Reports_Order Details] Details
ON Reports_Orders.OrderID = Details.OrderID
WHERE
YEAR(OrderDate) = @Year AND
(@Quarter = 0 OR
DATEPART(q, OrderDate) = @Quarter)
GROUP BY
DATEPART(y, OrderDate),
Reports_Orders.OrderID,
OrderDate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetSalesByRegion
(
@Year int,
@Quarter int
)
AS
Select
*, TotalMonth = Eastern + Western + Northern + Southern
From
(
Select DatePart(month, OrderDate) MonthOfSales,
Sum(Case RegionID When 1 Then (UnitPrice * Quantity ) Else 0 End) As Eastern,
Sum(Case RegionID When 2 Then (UnitPrice * Quantity ) Else 0 End) As Western,
Sum(Case RegionID When 3 Then (UnitPrice * Quantity ) Else 0 End) As Northern,
Sum(Case RegionID When 4 Then (UnitPrice * Quantity ) Else 0 End) As Southern
From Reports_Orders O
Inner Join Reports_Territories T On O.TerritoryID = T.TerritoryID
Inner Join [Reports_Order Details] OD On O.OrderID = OD.OrderID
Where Year(OrderDate) = @Year And DatePart(q, OrderDate) = @Quarter
Group By DatePart(month, OrderDate)
) As T
Order By MonthOfSales
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Reports_GetSalesByTerritory
(
@Year int = 1996
)
AS
SELECT t.TerritoryDescription,
SUM(d.UnitPrice * d.Quantity) as 'SalesTotals'
FROM Reports_Orders o
INNER JOIN [Reports_Order Details] d
ON o.OrderID = d.OrderID
INNER JOIN Reports_Territories t
ON o.TerritoryID = t.TerritoryID
WHERE
DATEPART(yy, o.OrderDate) = @Year
GROUP BY o.TerritoryID, t.TerritoryDescription
ORDER BY t.TerritoryDescription
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Submit an article or tip