First, let me mention I am aware I ***should*** not query the TfsWorkItemTracking database directly. Unfortunately I have needs that cannot be met by using the warehouse data; I need to create a simple list of product backlog items (work items) that are currently not assigned to a sprint (iteration), including the product backlog item's title, description, baseline work, and priority. As I mentioned I do not believe the description is available in the warehouse databases due to it being text, please correct me if I have been mislead.
Now, my question is regarding the WorkItemLongTexts table. I need to grab the description for a work item and I cannot find anywhere that stores the newest revision of a record in WorkItemLongTexts per entry in the WorkItemsAre table or WorkItemsLatestUsed view. If there is a way for me to avoid having to calculate the Max(Rev) of each entry in WorkItemLongTexts per WorkItemsLatestUsed please enlighten me.
Here is a query that may help demonstrate what I am after:
Code Snippet
SELECT
w.[System.Title],
W.[System.State],
W.[Microsoft.VSTS.Scheduling.BaselineWork] AS IdealTeamDays,
W.[Microsoft.eScrum.Common.Order] AS Priority,
w.[System.TeamProject],
w.[System.NodeName] AS TheProduct,
n.TheNewestRevision,
t.Words AS Description
FROM
dbo
.WorkItemsLatestUsed w
JOIN (
SELECT t.id, Max(t.Rev) AS TheNewestRevision
FROM dbo.WorkItemLongTexts t
JOIN dbo.WorkItemsLatestUsed w
ON w.[System.ID] = t.id
WHERE t.FldId = 52 -- Description field
AND [System.WorkItemType] = 'eScrum Product Backlog Item'
AND [System.State] not in ('Complete', 'Deleted')
AND w.[System.NodeName] = 'reporting'
GROUP BY
t.id
) n
ON n.id = w.[System.ID]
JOIN dbo.WorkItemLongTexts t
ON t.id = w.[System.ID]
AND t.Rev = n.TheNewestRevision
WHERE
t
.FldId
= 52
-- Description field
AND [System.WorkItemType] = 'eScrum Product Backlog Item'
AND [System.State] not in ('Complete', 'Deleted')
AND w.[System.NodeName] = 'reporting'
Thanx for your help in advance.
