Create table [analysis](
[deliverableid] int,
[description] varchar(255),
[startdate] datetime,
[deadline] datetime,
[itemtype] varchar(7));

Go


Create table [ChannelInfo](
[ProjectID] int,
[ChannelID] int,
[ChannelName] varchar(50),
[ChannelURL] varchar(255),
[StatusID] int,
[ChannelCommittedDate] datetime,
[LastUpdatedOn] datetime,
[LastUpdatedBy] varchar(15),
[StartDate] datetime,
[CompletedDate] datetime);

Go


Create table [ClientInfo](
[ClientID] int,
[ClientName] varchar(100),
[ClientAddress] varchar(100),
[PhoneNumber] varchar(15),
[FaxNumber] varchar(15),
[CreatedDate] datetime,
[LastUpdatedon] datetime,
[LastUpdatedBy] varchar(15));

Go


Create table [ClientTeam](
[ClientUserID] int,
[Name] varchar(50),
[Role] varchar(20),
[MailID] varchar(25),
[MessengerID] varchar(50),
[PhoneNumber] varchar(15),
[MobileNumber] varchar(15),
[ClientID] int,
[LastUpdatedOn] datetime);

Go


Create table [Deliverables](
[DeliverableID] int,
[MileStoneID] int,
[DeadLine] datetime,
[ItemID] int,
[PageID] int,
[ChannelID] int,
[AssociatedItemID] int);

Go


Create table [deliverables_view](
[deliverableid] int,
[milestoneid] int,
[deadline] datetime,
[associateditemid] int,
[projectid] int,
[projectname] varchar(50),
[ID] int,
[description] varchar(255),
[ItemType] varchar(7));

Go


Create table [dummy](
[UserID] int,
[UserName] varchar(50),
[UserRole] char(1),
[DomainID] varchar(100),
[ProjectID] int);

Go


Create table [items](
[itemID] int,
[Description] varchar(255),
[itemTypeID] int,
[projectid] int,
[CommittedDate] datetime);

Go


Create table [items_report](
[Name] varchar(255),
[ItemTypeName] varchar(20),
[Status] varchar(25),
[statusid] int,
[projectid] int);

Go


Create table [itemsAssociation](
[itemID] int,
[AssociatedItemID] int,
[AssociatedItemTypeID] int);

Go


Create table [ItemType](
[itemTypeID] int,
[ItemTypeName] varchar(20),
[TableNametouse] varchar(20),
[FieldNametouse] varchar(20));

Go


Create table [MileStoneInfo](
[MileStoneID] int,
[ProjectID] int,
[MileStoneName] varchar(50),
[MileStoneCommittedDate] datetime,
[Description] varchar(255),
[LastUpdatedOn] datetime,
[LastUpdatedBy] varchar(15));

Go


Create table [PageInfo](
[PageID] int,
[ChannelID] int,
[PageName] varchar(50),
[PageURL] varchar(255),
[StatusID] int,
[CommittedDate] datetime,
[Is_PageType] char(1));

Go


Create table [PageType](
[ClonePageID] int,
[PageTypeID] int,
[PageID] int);

Go


Create table [ProjectClientUserMapping](
[ProjectID] int,
[ClientUserID] int,
[LastUpdatedOn] datetime);

Go


Create table [ProjectInfo](
[ProjectID] int,
[ProjectName] varchar(50),
[Description] varchar(255),
[ClientID] int,
[EstimatedStartdate] datetime,
[EstimatedEndDate] datetime,
[CreatedOn] datetime,
[LastUpdatedOn] datetime,
[LastUpdatedBy] varchar(15));

Go


Create table [ReasonMaster](
[ReasonID] int,
[Reason] varchar(25));

Go


Create table [report_tasks](
[TaskId] int,
[itemtype] varchar(75),
[ItemID] int,
[PageID] int,
[ChannelID] int,
[ProjectID] int,
[Duration] varchar(10),
[UserID] int,
[AssignedOn] datetime,
[DeadLine] datetime,
[StartDate] datetime,
[TimeSpent] int,
[StatusID] int,
[TaskType] varchar(25),
[ReasonID] int,
[StartTime] datetime,
[EndTime] datetime,
[WorkStatus] varchar(50),
[AssignedBy] varchar(15),
[TaskMasterID] bit);

Go


Create table [StatusMaster](
[StatusID] int,
[statusDescription] varchar(25));

Go


Create table [TaskAssignment](
[TaskId] int,
[itemtype] varchar(75),
[ItemID] int,
[PageID] int,
[ChannelID] int,
[ProjectID] int,
[Duration] varchar(10),
[UserID] int,
[AssignedOn] datetime,
[DeadLine] datetime,
[StartDate] datetime,
[TimeSpent] int,
[StatusID] int,
[TaskType] varchar(25),
[ReasonID] int,
[StartTime] datetime,
[EndTime] datetime,
[WorkStatus] varchar(50),
[AssignedBy] varchar(15),
[IsAssigned] bit,
[TaskMasterID] int);

Go


Create table [taskList](
[taskid] int,
[Tasktype] varchar(25),
[Name] varchar(255),
[Reason] varchar(25),
[DeadLine] datetime,
[Duration] varchar(10),
[MinutesWorked] int,
[MinutesRemaining] int,
[Status] varchar(25),
[StartDate] datetime,
[AssignedOn] datetime,
[ItemType] varchar(75),
[UserName] varchar(50),
[projectid] int,
[userid] int,
[taskmasterid] int);

Go


Create table [TaskMaster](
[TaskMasterID] int,
[TaskName] varchar(50),
[TaskDescription] varchar(100),
[ProjectID] int,
[MinTime] varchar(10),
[ItemType] varchar(20),
[ParentTaskID] int);

Go


Create table [TaskStatus](
[Taskid] int,
[Taskdate] datetime,
[TaskHours] int);

Go


Create table [UserDetails](
[UserID] int,
[UserName] varchar(50),
[UserRole] char(1),
[DomainID] varchar(100),
[EmailAddress] varchar(100),
[ProjectID] int,
[UserStatus] char(1));

Go




create view "Customer and Suppliers by City" AS SELECT City, CompanyName, ContactName, 'Customers' AS Relationship FROM Customers UNION SELECT City, CompanyName, ContactName, 'Suppliers' FROM Suppliers --ORDER BY City, CompanyName

Go

create view "Alphabetical list of products" AS SELECT Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=0))

Go

create view "Current Product List" AS SELECT Product_List.ProductID, Product_List.ProductName FROM Products AS Product_List WHERE (((Product_List.Discontinued)=0)) --ORDER BY Product_List.ProductName

Go

create view "Orders Qry" AS SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Go

create view "Products Above Average Price" AS SELECT Products.ProductName, Products.UnitPrice FROM Products WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products) --ORDER BY Products.UnitPrice DESC

Go

create view "Products by Category" AS SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Products.Discontinued <> 1 --ORDER BY Categories.CategoryName, Products.ProductName

Go

create view "Quarterly Orders" AS SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'

Go

create view Invoices AS SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, (FirstName + ' ' + LastName) AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ( (Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID) ON Shippers.ShipperID = Orders.ShipVia

Go

create view "Order Details Extended" AS SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID --ORDER BY "Order Details".OrderID

Go

create view "Order Subtotals" AS SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal FROM "Order Details" GROUP BY "Order Details".OrderID

Go

create view "Product Sales for 1997" AS SELECT Categories.CategoryName, Products.ProductName, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID WHERE (((Orders.ShippedDate) Between '19970101' And '19971231')) GROUP BY Categories.CategoryName, Products.ProductName

Go

create view "Category Sales for 1997" AS SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales FROM "Product Sales for 1997" GROUP BY "Product Sales for 1997".CategoryName

Go

create view "Sales by Category" AS SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum("Order Details Extended".ExtendedPrice) AS ProductSales FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID) ON Products.ProductID = "Order Details Extended".ProductID) ON Categories.CategoryID = Products.CategoryID WHERE Orders.OrderDate BETWEEN '19970101' And '19971231' GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName --ORDER BY Products.ProductName

Go

create view "Sales Totals by Amount" AS SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate FROM Customers INNER JOIN (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) ON Customers.CustomerID = Orders.CustomerID WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')

Go

create view "Summary of Sales by Quarter" AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate IS NOT NULL --ORDER BY Orders.ShippedDate

Go

create view "Summary of Sales by Year" AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate IS NOT NULL --ORDER BY Orders.ShippedDate

Go

create procedure "Ten Most Expensive Products" AS SET ROWCOUNT 10 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC

Go

create procedure "Employee Sales by Country" @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount FROM Employees INNER JOIN (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) ON Employees.EmployeeID = Orders.EmployeeID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Go

create procedure "Sales by Year" @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Go

CREATE PROCEDURE CustOrdersDetail @OrderID int AS SELECT ProductName, UnitPrice=ROUND(Od.UnitPrice, 2), Quantity, Discount=CONVERT(int, Discount * 100), ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2) FROM Products P, [Order Details] Od WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

Go

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID

Go

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName

Go

CREATE PROCEDURE SalesByCategory @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName

Go

CREATE PROC saveXMLresponse @evaldata varchar(4000) AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@evaldata INSERT INTO Answers SELECT * FROM OPENXML (@hDoc,'/insert/Answers') WITH Answers

Go

CREATE PROCEDURE [Get_Customers_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, CompanyName nvarchar(40), ContactName nvarchar (30), ContactTitle nvarchar (30), Phone nvarchar (24), Fax nvarchar (24) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( CompanyName, ContactName, ContactTitle, Phone, Fax ) SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter SELECT @TotalRecords = COUNT(*) FROM Customers

Go