Thanks for the replies.

After applying the MAX aggregate to the [LastSelect] column, the second part of my dilemma was solved by creating another column with the ten digit date using the CONVERT function. It gave me specific items to pivot on. For giggles I threw in the 'day of the week' column.

Here's the updated DDL and some sample data in case this could help someone else:

CREATE TABLE [dba_admin].[dbo].[LastSelectCheck](

[TableName] [nvarchar](128) NULL,

[LastSelect] [datetime] NULL,

[RunDate] [datetime] NOT NULL,

[Server] [nvarchar](128) NULL,

[DotW] [int] NULL, -- Day of the week

[TenDigDate] [char](10) NULL -- Ten Digit Date

)

-- Top part of the CTE is from http://beyondrelational.com/modules/1/justlearned/0/tips/9111/sql-server-finding-last-accessed-time-for-tables-by-queries-or-find-unused-tables.aspx

WITH LastActivity (ObjectID, LastAction) AS

(

SELECT OBJECT_ID AS TableName,

last_user_seek AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

UNION

SELECT OBJECT_ID AS TableName,

last_user_scan AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

UNION

SELECT OBJECT_ID AS TableName,

last_user_lookup AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

)

INSERT INTO [dba_admin].[dbo].[LastSelectCheck]

SELECT OBJECT_NAME(so.OBJECT_ID) AS TableName,

MAX(la.LastAction) AS LastSelect,

getdate() as 'RunDate',

@@servername as 'Server',

datepart(dw, getdate()) as 'DotW',

convert(char(10), getdate(), 112) as 'TenDigDate'

FROM sys.objects so

LEFT JOIN LastActivity la

ON so.OBJECT_ID = la.ObjectID

WHERE so.TYPE = 'U'

AND so.OBJECT_ID > 100

GROUP BY OBJECT_NAME(so.OBJECT_ID)

ORDER BY OBJECT_NAME(so.OBJECT_ID)

Find which days the table has been accessed:

select tablename, [20130408], [20130409], [20130410]

from

(

select tablename, LastSelect

,TenDigDate

from LastSelectCheck

where tablename like 'ACCOUNT%'

) as LSC

pivot

(

max(LastSelect)

for TenDigDate in ([20130408], [20130409], [20130410])

) as P