After reading Tom Fischer's blog entry on How to pull all Pending Changes (Checked Out Items) from TFS 2005 or 2008 I thought I would look into how to accomplish the same objective using SSRS and SQL.
With more research on the TfsVersionControl database and also reviewing a query on retrieving data from a TFS database, I put together this sql statement that retrieves a list of users who have items checked out.
/* This query returns the
list of currently checked out items */
declare @UtcDiff int,@Date datetime
,@Offset int
--set the time difference, date, and number of dates back you want to go
select @UtcDiff=datediff(millisecond,getutcdate(), getdate())
,@Date=convert(datetime,convert(varchar(20),getdate(),101))
,@Offset=5
select i.displayname as UserID
,replace(replace(pc.TargetParentPath+replace(pc.targetChildItem,'\',''),'"','-'),'>','_') as TFSFile
,pc.PendingCommand as CommandType
,dateadd(millisecond,@UtcDiff,pc.CreationDate) as LocalTime
from dbo.tbl_PendingChange pc
inner join dbo.tbl_Workspace w on w.workspaceid=pc.workspaceid
inner join dbo.tbl_Identity i on w.ownerid=i.identityid
where pc.creationdate<=dateadd(day,-convert(int,@Offset),@Date)
and w.computer is not null
order by i.displayname,pc.creationdate
Please note the second to last line in the query "and w.computer is not null". The tbl_PendingChange stores all checked out and shelved items. If you remove the "not" and add the tbl_workspace columns "WorkspaceName" and "Comment" (see query below) you can get a list of what items have been shelved and when.
/* This query returns the
list of current shelf sets */
declare @UtcDiff int,@Date datetime
,@Offset int
--set the time difference, date, and number of dates back you want to go
select @UtcDiff=datediff(millisecond,getutcdate(), getdate())
,@Date=convert(datetime,convert(varchar(20),getdate(),101))
,@Offset=5
select i.displayname as UserID
,replace(replace(pc.TargetParentPath+replace(pc.targetChildItem,'\',''),'"','-'),'>','_') as TFSFile
,pc.PendingCommand as CommandType
,dateadd(millisecond,@UtcDiff,pc.CreationDate) as LocalTime
,w.WorkspaceName
,w.Comment
from dbo.tbl_PendingChange pc
inner join dbo.tbl_Workspace w on w.workspaceid=pc.workspaceid
inner join dbo.tbl_Identity i on w.ownerid=i.identityid
where pc.creationdate<=dateadd(day,-convert(int,@Offset),@Date)
and w.computer is null
order by i.displayname,pc.creationdate