SQLDCH (4/12/2013)

---

I have a table that stores a tablename, the last time the table was accessed and when that data was put into the table:

CREATE TABLE [dbo].[LastSelectCheck](

[TableName] [nvarchar](128) NULL,

[LastSelect] [datetime] NULL,

[RunDate] [datetime] NOT NULL,

[Server] [nvarchar](128) NULL

) ON [PRIMARY]

GO

It’s populated every night by a select from sys.dm_db_index_usage_stats.

My question: I’d like to run a query that shows the last select over a week’s time, like this:

Tablename day1 day2 day3

--------------------------------------------------------------------------------

tblOrder 2013-04-08 NULL 2013-04-10

tblOrder2 2013-04-08 2013-04-09 NULL

I realize I can do this thru a series of CTE, temp tables or joins but it seems that there’s something more elegant out there. I tried PIVOT but it doesn’t look like it’ll work here as I'm not aggregating and the columns are not items from the tables.

Any advice? TIA.

Sample data:

insert into LastSelectCheck2 values ('tblOrder', NULL, '2013-04-08', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder2', '2013-04-08', '2013-04-08', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder3', NULL, '2013-04-08', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder4', '2013-04-08', '2013-04-08', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder', '2013-04-09', '2013-04-09', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder2', '2013-04-09', '2013-04-09', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder3', '2013-04-09', '2013-04-09', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder4', '2013-04-09', '2013-04-09', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder', '2013-04-10', '2013-04-10', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder2', '2013-04-10', '2013-04-10', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder3', NULL, '2013-04-10', 'MyServer1')

insert into LastSelectCheck2 values ('tblOrder4', NULL, '2013-04-10', 'MyServer1')