Listing 4, Inside the TFS Databases, Part 2
- By Jeff Levinson
- 03/16/2009
with tempData as
(
--Get the long text values
select x.ID,
y.Name,
x.Words,
c.Rev
from WorkItemLongTexts x
join Fields y on x.FldID = y.FldID
join (select b.Name,
a.ID,
max(a.Rev) as Rev
from WorkItemLongTexts a
join Fields b on a.FldID = b.FldID
group by b.Name,
a.ID) c on c.Name = y.Name
and c.Rev = x.Rev
)
--Get the base work item
select a.ID,
a.[Team Project],
c.Words as 'Description',
d.Words as '[Whatever other custom fields you have]',
b.*
from xxTree a
join WorkItemsAre b on a.ID = b.AreaID
join tempData c on b.ID = c.ID and c.Name = 'Description'
join tempData d on b.ID = d.ID and d.Name = '[whatever other custom field names you have]'
where a.[Team Project] = @Project
About the Author
Jeff Levinson is the Application Lifecycle Management practice lead for Northwest Cadence specializing in process and methodology. He is the co-author of "Pro Visual Studio Team System with Database Professionals" (Apress 2007), the author of "Building Client/Server Applications with VB.NET" (Apress 2003) and has written numerous articles. He is an MCAD, MCSD, MCDBA, MCT and is a Team System MVP. He has a Masters in Software Engineering from Carnegie Mellon University and is a former Solutions Design and Integration Architect for The Boeing Company. You can reach him at [email protected].