SELECT     object_name, database_name, DATEPART(Week, event_time) AS EventWeek, SUM(sequence_number) AS [#TimesAccessed]
FROM         SQLPLEX_AUDITLOG_ALL WITH (nolock)
WHERE     (database_name NOT LIKE 'Lite%') AND (class_type_desc IN ('view', 'table')) AND (server_machine_name = 'wpnoa1gNpdbpr') AND 
                      (sequence_number = 1) AND (schema_name = 'DBO') AND (event_time BETWEEN '11/01/2012' AND '11/07/2012')
GROUP BY object_name, database_name, DATEPART(Week, event_time)

ORDER BY [#TimesAccessed] DESC, EventWeek DESC

CREATE TABLE [dbo].[SQLPLEX_AUDITLOG_ALL](
	[SQLPlexAuditLogID] [bigint] IDENTITY(1,1) NOT NULL,
	[SQLPlexAuditLogID_Local] [bigint] NULL,
	[event_time] [datetime2](7) NOT NULL,
	[sequence_number] [int] NOT NULL,
	[database_type] [varchar](13) NOT NULL,
	[server_instance_name] [nvarchar](128) NULL,
	[server_machine_name] [sql_variant] NULL,
	[server_ip_address] [varchar](15) NOT NULL,
	[client_host_name] [nvarchar](128) NULL,
	[client_network_protocol] [nvarchar](40) NULL,
	[client_ip_address] [varchar](48) NULL,
	[client_login] [nvarchar](128) NULL,
	[session_server_principal_name] [nvarchar](128) NULL,
	[server_principal_name] [nvarchar](128) NULL,
	[database_principal_name] [nvarchar](128) NULL,
	[client_Authentication] [nvarchar](40) NULL,
	[database_name] [nvarchar](128) NULL,
	[schema_name] [nvarchar](128) NULL,
	[object_name] [nvarchar](128) NULL,
	[securable_class_desc] [nvarchar](35) NULL,
	[class_type_desc] [nvarchar](35) NULL,
	[action_name] [nvarchar](128) NULL,
	[statement] [nvarchar](max) NULL,
	[program_name] [varchar](1000) NULL,
	[error_message] [varchar](1000) NULL,
	[ByEnd] [varchar](1) NULL,
	[NPI_SchemaName] [varchar](500) NULL,
	[NPI_TableName] [varchar](500) NULL,
	[NPI_ColumnName] [varchar](500) NULL,
	[NPI_ColumnName_Accessed] [varchar](500) NULL,
	[DatabaseRoleName] [varchar](50) NULL,
	[MemberName] [varchar](50) NULL,
	[Process] [varchar](50) NULL,
	[ReadDefinition] [varchar](50) NULL,
	[Read] [varchar](50) NULL,
	[Administer] [varchar](50) NULL,
	[Reader]  AS ([read]),
PRIMARY KEY CLUSTERED 
(
	[SQLPlexAuditLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SELECT     object_name, database_name, DATEPART(Week, event_time) AS EventWeek, SUM(sequence_number) AS [#TimesAccessed],
    DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0) AS [EventHour]	
INTO #EventHour
FROM         SQLPLEX_AUDITLOG_ALL WITH (nolock)
WHERE     (database_name NOT LIKE 'Lite%') AND (class_type_desc IN ('view', 'table')) AND (server_machine_name = 'wpnoa1gNpdbpr') AND
		  (sequence_number = 1) AND (schema_name = 'DBO') AND 
                (event_time >= '20121101' AND event_time < '20121108')
GROUP BY object_name, database_name, DATEPART(Week, event_time), DATEADD(HOUR, DATEDIFF(HOUR, 0, event_time), 0)

CREATE CLUSTERED INDEX object_db_week ON #EventHour ( object_name, database_name, EventWeek )
     
SELECT object_name, database_name, EventWeek, [#TimesAccessed],
    (SELECT TOP (1) EventHour FROM #EventHour eh2 WHERE eh2.object_name = eh1.object_name AND
     eh2.database_name = eh1.database_name AND eh2.EventWeek = eh1.EventWeek ORDER BY [#TimesAccessed] DESC) AS EventHourMostAccessed
FROM (     
	SELECT     object_name, database_name, EventWeek, SUM([#TimesAccessed]) AS [#TimesAccessed]
	FROM         #EventHour eh1 WITH (NOLOCK)
	GROUP BY object_name, database_name, EventWeek
) AS derived