/****** Object:  Database Wrox    Script Date: 9/24/2000 1:37:54 AM ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Wrox')
	DROP DATABASE [Wrox]
GO

CREATE DATABASE [Wrox]  ON (NAME = N'Wrox_System', FILENAME = N'C:\Wrox\Data\WroxReports\Wrox_System.MDF' , SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'Wrox_Log', FILENAME = N'C:\Wrox\Data\WroxReports\Wrox_Log.LDF' , SIZE = 5, FILEGROWTH = 10%)

GO
ALTER DATABASE [Wrox] ADD FILEGROUP [DATA] 
GO
ALTER DATABASE [Wrox] ADD FILE(NAME = N'Wrox_Data', FILENAME = N'C:\Wrox\Data\WroxReports\Wrox_Data.NDF' , SIZE = 3, FILEGROWTH = 10%) TO FILEGROUP [DATA]
GO
ALTER DATABASE [Wrox] ADD FILEGROUP [ARCHIVE] 
GO
ALTER DATABASE [Wrox] ADD FILE(NAME = N'Wrox_Archive', FILENAME = N'C:\Wrox\Data\WroxReports\Wrox_Archive.NDF' , SIZE = 1, FILEGROWTH = 10%) TO FILEGROUP [ARCHIVE]
GO

exec sp_dboption N'Wrox', N'autoclose', N'false'
GO

exec sp_dboption N'Wrox', N'bulkcopy', N'false'
GO

exec sp_dboption N'Wrox', N'trunc. log', N'false'
GO

exec sp_dboption N'Wrox', N'torn page detection', N'false'
GO

exec sp_dboption N'Wrox', N'read only', N'false'
GO

exec sp_dboption N'Wrox', N'dbo use', N'false'
GO

exec sp_dboption N'Wrox', N'single', N'false'
GO

exec sp_dboption N'Wrox', N'autoshrink', N'false'
GO

exec sp_dboption N'Wrox', N'ANSI null default', N'false'
GO

exec sp_dboption N'Wrox', N'recursive triggers', N'false'
GO

exec sp_dboption N'Wrox', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Wrox', N'concat null yields null', N'false'
GO

exec sp_dboption N'Wrox', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Wrox', N'default to local cursor', N'false'
GO

exec sp_dboption N'Wrox', N'quoted identifier', N'false'
GO

exec sp_dboption N'Wrox', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Wrox', N'auto create statistics', N'true'
GO

exec sp_dboption N'Wrox', N'auto update statistics', N'true'
GO

use [Wrox]
GO

/****** Object:  Stored Procedure dbo.spReportActivityByWeek    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spReportActivityByWeek]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spReportActivityByWeek]
GO

/****** Object:  Stored Procedure dbo.spReportActivityForDay    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spReportActivityForDay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spReportActivityForDay]
GO

/****** Object:  Stored Procedure dbo.spReportActivityYTD    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spReportActivityYTD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spReportActivityYTD]
GO

/****** Object:  Stored Procedure dbo.spReportActivityByDayByWeekForYTD    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spReportActivityByDayByWeekForYTD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spReportActivityByDayByWeekForYTD]
GO

/****** Object:  View dbo.vwStatisticsByDay    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwStatisticsByDay]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwStatisticsByDay]
GO

/****** Object:  View dbo.vwStatisticsByHour    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwStatisticsByHour]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwStatisticsByHour]
GO

/****** Object:  View dbo.vwStatisticsByWeek    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwStatisticsByWeek]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwStatisticsByWeek]
GO

/****** Object:  Table [dbo].[StatisticsByHourByUser]    Script Date: 9/24/2000 1:37:58 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StatisticsByHourByUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StatisticsByHourByUser]
GO

/****** Object:  User WroxReport    Script Date: 9/24/2000 1:37:55 AM ******/
if not exists (select * from dbo.sysusers where name = N'WroxReport' and uid < 16382)
	EXEC sp_grantdbaccess N'WroxReport', N'WroxReport'
GO

/****** Object:  User WroxReport    Script Date: 9/24/2000 1:37:55 AM ******/
exec sp_addrolemember N'db_datareader', N'WroxReport'
GO

/****** Object:  Table [dbo].[StatisticsByHourByUser]    Script Date: 9/24/2000 1:38:00 AM ******/
CREATE TABLE [dbo].[StatisticsByHourByUser] (
	[Period] [datetime] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[MinutesOnSite] [float] NOT NULL ,
	[NumberOfPagesVisited] [int] NOT NULL ,
	[NumberOfItemsAddedToCart] [int] NOT NULL ,
	[TotalValueOfCartItems] [float] NULL ,
	[SalesCompleted] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StatisticsByHourByUser] WITH NOCHECK ADD 
	CONSTRAINT [PK_StatisticsByHourByUser] PRIMARY KEY  CLUSTERED 
	(
		[Period],
		[UserID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_StatisticsByHourByUser_byUser] ON [dbo].[StatisticsByHourByUser]([UserID]) WITH  FILLFACTOR = 80 ON [DATA]
GO

GRANT  SELECT  ON [dbo].[StatisticsByHourByUser]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.vwStatisticsByDay    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE VIEW dbo.vwStatisticsByDay
AS

SELECT 
	A.DayOfYear,
	UserCount,   
	MinutesOnsiteMax,                                      
	MinutesOnsiteMin,                                      
	MinutesOnsiteAvg,                                      
	NumberOfPagesVisitedMax, 
	NumberOfPagesVisitedMin, 
	NumberOfPagesVisitedAvg,                               
	NumberOfItemsAddedToCartMax, 
	NumberOfItemsAddedToCartMin, 
	NumberOfItemsAddedToCartAvg,                           
	TotalValueOfCartItemsMax,                              
	TotalValueOfCartItemsMin,                              
	TotalValueOfCartItemsAvg,                              
	SalesCompletedCount                                   

FROM
(
	SELECT 
		Convert(datetime, CONVERT(varchar(14), Period, 110)) AS DayOfYear, 
		UserCount = Count(UserID),
		MinutesOnsiteMax = MAX(MinutesOnSite), 
		MinutesOnsiteMin = MIN(MinutesOnSite), 
   		MinutesOnsiteAvg = AVG(MinutesOnSite), 
   		NumberOfPagesVisitedMax = MAX(NumberOfPagesVisited), 
   		NumberOfPagesVisitedMin = MIN(NumberOfPagesVisited), 
   		NumberOfPagesVisitedAvg = AVG(NumberOfPagesVisited), 
   		NumberOfItemsAddedToCartMax = MAX(NumberOfItemsAddedToCart),
    		TotalValueOfCartItemsMax = MAX(TotalValueOfCartItems), 
   		SalesCompletedCount =SUM(SalesCompleted)
	FROM 
		StatisticsByHourByUser
	GROUP BY 
		Convert(datetime, CONVERT(varchar(14), Period, 110)) 
) As A
LEFT JOIN
(
	SELECT 
		Convert(datetime, CONVERT(varchar(14), Period, 110)) AS DayOfYear, 
    		NumberOfItemsAddedToCartMin = MIN(NumberOfItemsAddedToCart),
    		NumberOfItemsAddedToCartAvg = AVG(CONVERT(float,NumberOfItemsAddedToCart)),
   		TotalValueOfCartItemsAvg = AVG(CONVERT(float,TotalValueOfCartItems)), 
   		TotalValueOfCartItemsMin = MIN(TotalValueOfCartItems)
	FROM 
		StatisticsByHourByUser
	WHERE
		NumberOfItemsAddedToCart > 0
	GROUP BY 
		Convert(datetime, CONVERT(varchar(14), Period, 110)) 
) As B
ON (A.DayOfYear = B.DayOfYear)


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[vwStatisticsByDay]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.vwStatisticsByHour    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE VIEW dbo.vwStatisticsByHour
AS

SELECT 
	A.Period,
	UserCount,   
	MinutesOnsiteMax,                                      
	MinutesOnsiteMin,                                      
	MinutesOnsiteAvg,                                      
	NumberOfPagesVisitedMax, 
	NumberOfPagesVisitedMin, 
	NumberOfPagesVisitedAvg,                               
	NumberOfItemsAddedToCartMax, 
	NumberOfItemsAddedToCartMin, 
	NumberOfItemsAddedToCartAvg,                           
	TotalValueOfCartItemsMax,                              
	TotalValueOfCartItemsMin,                              
	TotalValueOfCartItemsAvg,                              
	SalesCompletedCount                                   

FROM
(
	SELECT 
		Period,
		UserCount = Count(UserID),
		MinutesOnsiteMax = MAX(MinutesOnSite), 
		MinutesOnsiteMin = MIN(MinutesOnSite), 
   		MinutesOnsiteAvg = AVG(MinutesOnSite), 
   		NumberOfPagesVisitedMax = MAX(NumberOfPagesVisited), 
   		NumberOfPagesVisitedMin = MIN(NumberOfPagesVisited), 
   		NumberOfPagesVisitedAvg = AVG(NumberOfPagesVisited), 
   		NumberOfItemsAddedToCartMax = MAX(NumberOfItemsAddedToCart),
    		TotalValueOfCartItemsMax = MAX(TotalValueOfCartItems), 
   		SalesCompletedCount =SUM(SalesCompleted)
	FROM 
		StatisticsByHourByUser
	GROUP BY 
		Period
) As A
LEFT JOIN
(
	SELECT 
		Period,
    		NumberOfItemsAddedToCartMin = MIN(NumberOfItemsAddedToCart),
    		NumberOfItemsAddedToCartAvg = AVG(CONVERT(float,NumberOfItemsAddedToCart)),
   		TotalValueOfCartItemsAvg = AVG(CONVERT(float,TotalValueOfCartItems)), 
   		TotalValueOfCartItemsMin = MIN(TotalValueOfCartItems)
	FROM 
		StatisticsByHourByUser
	WHERE
		NumberOfItemsAddedToCart > 0
	GROUP BY 
		Period
) As B
ON (A.Period = B.Period)


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[vwStatisticsByHour]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.vwStatisticsByWeek    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE VIEW dbo.vwStatisticsByWeek
AS

SELECT 
	A.WeekBeginning,                   
	UserCount,   
	MinutesOnsiteMax,                                      
	MinutesOnsiteMin,                                      
	MinutesOnsiteAvg,                                      
	NumberOfPagesVisitedMax, 
	NumberOfPagesVisitedMin, 
	NumberOfPagesVisitedAvg,                               
	NumberOfItemsAddedToCartMax, 
	NumberOfItemsAddedToCartMin, 
	NumberOfItemsAddedToCartAvg,                           
	TotalValueOfCartItemsMax,                              
	TotalValueOfCartItemsMin,                              
	TotalValueOfCartItemsAvg,                              
	SalesCompletedCount                                   

FROM
(
	SELECT 
		CONVERT(datetime,CONVERT(varchar(14), DATEADD(dd,    - 1 * DATEPART(dw, Period) + 1, Period), 110) )   AS WeekBeginning, 
		UserCount = Count(UserID),
		MinutesOnsiteMax = MAX(MinutesOnSite), 
		MinutesOnsiteMin = MIN(MinutesOnSite), 
   		MinutesOnsiteAvg = AVG(MinutesOnSite), 
   		NumberOfPagesVisitedMax = MAX(NumberOfPagesVisited), 
   		NumberOfPagesVisitedMin = MIN(NumberOfPagesVisited), 
   		NumberOfPagesVisitedAvg = AVG(NumberOfPagesVisited), 
   		NumberOfItemsAddedToCartMax = MAX(NumberOfItemsAddedToCart),
    		TotalValueOfCartItemsMax = MAX(TotalValueOfCartItems), 
   		SalesCompletedCount =SUM(SalesCompleted)
	FROM 
		StatisticsByHourByUser
	GROUP BY 
		CONVERT(datetime,CONVERT(varchar(14), DATEADD(dd,    - 1 * DATEPART(dw, Period) + 1, Period), 110) )
) As A
LEFT JOIN
(
	SELECT 
		CONVERT(datetime,CONVERT(varchar(14), DATEADD(dd,    - 1 * DATEPART(dw, Period) + 1, Period), 110) )   AS WeekBeginning, 
    		NumberOfItemsAddedToCartMin = MIN(NumberOfItemsAddedToCart),
    		NumberOfItemsAddedToCartAvg = AVG(CONVERT(float,NumberOfItemsAddedToCart)),
   		TotalValueOfCartItemsAvg = AVG(CONVERT(float,TotalValueOfCartItems)), 
   		TotalValueOfCartItemsMin = MIN(TotalValueOfCartItems)
	FROM 
		StatisticsByHourByUser
	WHERE
		NumberOfItemsAddedToCart > 0
	GROUP BY 
		CONVERT(datetime,CONVERT(varchar(14), DATEADD(dd,    - 1 * DATEPART(dw, Period) + 1, Period), 110) )
) As B
ON (A.WeekBeginning = B.WeekBeginning)


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[vwStatisticsByWeek]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.spReportActivityByDayByWeekForYTD    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE PROCEDURE spReportActivityByDayByWeekForYTD
(
	@WeekEnding  datetime
)
AS

DECLARE @dtCurrentDate datetime
DECLARE @dtEndDate datetime
DECLARE @iYear int
 
       
    	-- Initialize environment.	
	SET  ANSI_WARNINGS  OFF
	SET NOCOUNT ON

	-- Process arguments.
	Select @dtEndDate = DateAdd(dd, -6,@WeekEnding)

	-- Derive related values
	Select @iYear = datepart(yyyy, @WeekEnding)


	-- Create results table.
	CREATE TABLE #R4_Results (
		[RecordType] int default (0),
		[PeriodBeginning] [datetime] NOT NULL ,
		[YearOfPeriod] [int] NOT NULL,
		[WeekOfYear] [int] NOT NULL ,
		[DayOfYear] [int] NOT NULL ,		
	) 
	CREATE CLUSTERED  INDEX R4_Results ON #R4_Results ( [WeekOfYear], [DayOfYear], [YearOfPeriod] )  
	
	Select @dtCurrentDate = @dtEndDate
	While DatePart(yyyy, @dtCurrentDate) = @iYear
		BEGIN
		Insert #R4_Results ( [PeriodBeginning] , [YearOfPeriod] , [WeekOfYear] , [DayOfYear] )
			Select Distinct
				@dtCurrentDate, 
				DatePart(yyyy,@dtCurrentDate), 
				DatePart(week, @dtCurrentDate),
				DatePart(dy, @dtCurrentDate)
		Select @dtCurrentDate = dateadd (wk, -1, @dtCurrentDate )
	        END


	Select 
		RecordType, PeriodBeginning, S.*
	 from #R4_Results as C Inner JOIN  vwStatisticsByDay as S
		On (C.[WeekOfYear] = DatePart(wk,S.[DayOfYear]))

	Union

	Select 
		1, PeriodBeginning, S.*
	 from #R4_Results as C Inner JOIN  vwStatisticsByWeek as S
		On (C.[PeriodBeginning] = S.[WeekBeginning])


	Order By  PeriodBeginning, RecordType, S.[DayOfYear]

	-- Reset environment
	SET  ANSI_WARNINGS  ON
	SET NOCOUNT OFF

	RETURN @@ERROR


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[spReportActivityByDayByWeekForYTD]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.spReportActivityByWeek    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE PROCEDURE [spReportActivityByWeek]
(
	@dtPeriodEnding datetime
) AS

	Select 0 as RecordType, *
	From vwStatisticsByDay
	Where
		DatePart(wk,DayOfYear) = DatePart(wk, @dtPeriodEnding)
		and
		DatePart(yy,DayOfYear) = DatePart(yy, @dtPeriodEnding)
	Union
	Select 1 as RecordType, *
	From vwStatisticsByWeek
	Where
		DatePart(wk,WeekBeginning) = DatePart(wk, @dtPeriodEnding)
		and
		DatePart(yy,WeekBeginning) = DatePart(yy, @dtPeriodEnding)
	Order By 
		RecordType, DayOfYear
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[spReportActivityByWeek]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.spReportActivityForDay    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE PROCEDURE [spReportActivityForDay] 
(
	@dtDateToProcess datetime
)
AS
BEGIN

	Select 0 as RecordType, *
	From vwStatisticsByHour
	Where
		DatePart(dy,Period) = DatePart(dy,@dtDateToProcess)
		and
		DatePart(yy,Period) = DatePart(yy,@dtDateToProcess)
	Union
	Select 1 as RecordType, *
	From vwStatisticsByDay
	Where
		DatePart(dy,DayOfYear) = DatePart(dy,@dtDateToProcess)
		and
		DatePart(yy,DayOfYear) = DatePart(yy,@dtDateToProcess)
	Order By 
		RecordType,Period

END


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[spReportActivityForDay]  TO [WroxReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.spReportActivityYTD    Script Date: 9/24/2000 1:38:01 AM ******/
CREATE PROCEDURE [spReportActivityYTD]
(
	@dtPeriodEnding datetime
) AS

	Select 0 as RecordType, *
	From 
		vwStatisticsByWeek
	Where
		DatePart(wk,WeekBeginning) <= DatePart(wk,@dtPeriodEnding)
		and
		DatePart(yy,WeekBeginning) = DatePart(yy,@dtPeriodEnding)
	Union

	Select 
		1 as RecordType, 
		@dtPeriodEnding,               
		Sum(UserCount  ),
		Max(MinutesOnsiteMax ),
		Min(MinutesOnsiteMin ),                                     
		Avg(MinutesOnsiteAvg ),                                     
		Max(NumberOfPagesVisitedMax ),
		Min(NumberOfPagesVisitedMin ),
		Avg(NumberOfPagesVisitedAvg ),
		Max(NumberOfItemsAddedToCartMax ),
		Min(NumberOfItemsAddedToCartMin ),
		Avg(NumberOfItemsAddedToCartAvg ),                          
		Max(TotalValueOfCartItemsMax    ),                          
		Min(TotalValueOfCartItemsMin    ),                          
		Avg(TotalValueOfCartItemsAvg    ),                          
		Sum(SalesCompletedCount )
	From 
		vwStatisticsByWeek
	Where
		DatePart(wk,WeekBeginning) <= DatePart(wk,@dtPeriodEnding)
		and
		DatePart(yy,WeekBeginning) = DatePart(yy,@dtPeriodEnding)
	Order By 
		RecordType,WeekBeginning


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[spReportActivityYTD]  TO [WroxReport]
GO

