Why won't Johnny enter his time (how to get time entry information out of TFS)?

Because it's a pain - that's why. He has to enter it 3 different places using 3 different tools. Wouldn't it be nice if he only had to use the tool that's already integrated into Visual Studio? The tool that tracks his work items all the way back to his code check-ins?

Why not just use the time entry system you have? It works, right? Because the metrics you get out of your time system probably don't integrate with MS Excel, SQL Reporting Services or get put in a warehouse for easy reporting. Because the rest of your project metrics are in Team System and the one bit of information you need to make them complete are somewhere in a 10 year old classic ASP application with a crazy clunky interface.

Wanna try? Sure you do. It's easy.

Open SQL Management Studio and connect to your favorite TFS Warehouse. Don't have access to the warehouse? Get it. Connect to the warehouse and run this:

/*Create a temp table to hold values from the two queries needed to fetch this information*/

CREATE TABLE #TFSTime(
Person nvarchar(256)
,Change float)

/*Declare variables common in both queries*/

DECLARE @StartDate Datetime
DECLARE @EndDate DateTime
DECLARE @TeamProject int

/*Set the date range and project*/

SET @StartDate = '7/16/2010';
SET @EndDate = GETDATE();
SET @TeamProject = '25';


Insert Into #TFSTime

/*Insert tasks where the user entered a value in completed work and created the task at the same time*/

SELECT P.Person
,WIH2.[_Microsoft_VSTS_Scheduling_CompletedWork]as Change

FROM [TfsWarehouse].[dbo].[Work Item] as WI2

Inner Join [TfsWarehouse].[dbo].[Work Item History] as WIH2
on WI2.[__ID] = WIH2.[Work item]

Inner Join [TfsWarehouse].[dbo].[Person] as P
on P.[__ID] = WIH2.[Changed By]

Inner Join [TfsWarehouse].[dbo].[Date] as D
on D.[UTCDateTime] = WIH2.[Date]

Where

WIH2.[Team Project] = @TeamProject
AND WIH2.[Revision Count] = 1
AND WIH2.[Date] > @StartDate
AND WIH2.[Date] < @EndDate
AND RIGHT(WI2.[Work Item],2) Like '-1'
AND WIH2.[_Microsoft_VSTS_Scheduling_CompletedWork] <> 0

ORDER BY WIH2.[Date]

Insert Into #TFSTime

/*Insert time from tasks where the user updated completed work*/

SELECT P.Person

,(WIH2.[_Microsoft_VSTS_Scheduling_CompletedWork]-WIH1.[_Microsoft_VSTS_Scheduling_CompletedWork]) as Change

FROM [TfsWarehouse].[dbo].[Work Item] as WI1

Left Outer Join [TfsWarehouse].[dbo].[Work Item] as WI2
on WI1.System_ID = WI2.System_ID
AND WI1.System_Rev = (WI2.System_Rev-1)

Inner Join [TfsWarehouse].[dbo].[Work Item History] as WIH1
on WI1.[__ID] = WIH1.[Work item]

Inner Join [TfsWarehouse].[dbo].[Work Item History] as WIH2
on WI2.[__ID] = WIH2.[Work item]

Inner Join [TfsWarehouse].[dbo].[Person] as P
on P.[__ID] = WIH2.[Changed By]

Inner Join [TfsWarehouse].[dbo].[Date] as D
on D.[UTCDateTime] = WIH2.[Date]

Where

WIH2.[Team Project] = @TeamProject
AND WIH2.[Revision Count] = 1
AND WIH1.[Revision Count] = 1
AND (WIH2.[_Microsoft_VSTS_Scheduling_CompletedWork]-WIH1.[_Microsoft_VSTS_Scheduling_CompletedWork])<>0

AND WIH2.[Date] > @StartDate
AND WIH2.[Date] < @EndDate

Select SUM (Change)as Time_Entered
,Person

From #TFSTime

Group By Person

Drop Table #TFSTime

Things you might want to change are highlighted in yellow. Replace the team project with your team project number (query the project table to see what that is if you don't know).

---------------
Updated 7/19/2010 - I updated the query to account for times when a person creates a task and puts time in the completed work field at the same time.