Some work item history is stored in the cube and additional information is stored in the Warehouse. What question are you trying to answer with your report
There may be a better way of doing this - and I haven't fully tested these, but for your example the queries below should get you started:
"How many priority 1 bugs were opened that eventually got changed to a priority 2 or 3 " I translated to: How many bugs are there which are revised and do not currently have priority 1 - and the 1st revision of the bug had priority 1.
Two Relational queries:
1) Using intersect: use [TfsWarehouse] select [Work Item].[System_Id] from [Work Item] where [Work Item].[Microsoft_VSTS_Common_Priority] = 1 and [Work Item].[System_Rev] = 1 intersect select [Current Work Item].[__TrackingId] from [Current Work Item] join [Work Item] on [Current Work Item].[Work Item] = [Work Item].[__ID] where [Work Item].[Microsoft_VSTS_Common_Priority] <> 1
2) Using subquery:
use [TfsWarehouse] select [Work Item].[Microsoft_VSTS_Common_Priority], [Work Item].[System_Title] from [Current Work Item] join [Work Item] on [Current Work Item].[Work Item] = [Work Item].[__ID] where [Work Item].[System_Rev] <> 1 and [Work Item].[Microsoft_VSTS_Common_Priority] <> 1 and [Current Work Item].[__TrackingId] in ( select [Work Item].[System_Id] from [Work Item] where [System_Rev] = 1 and [Microsoft_VSTS_Common_Priority] = 1 )
These do not exclude closed bugs.
|