查找过去 30 天内分配给 X 的工作项 [英] Find WorkItems that were assigned to X in the last 30 days

问题描述

我正在尝试查找过去 30 天内分配给某个人 X 的所有工作项.
我遇到的大问题是过去 30 天"部分.

I'm trying to find all WorkItems that were assigned to a person X in the last 30 days.
The big problem I have is the "in the last 30 days"-part.

我考虑过使用ever"或asof"关键字，但还没有找到好的答案..类似于 WHERE [Assigned To] = 'X' AND (([Assigned To] != 'X') asof '<30daysago>').
但这仍然不是万无一失的解决方案.

I thought about using the "ever" or "asof" keywords, but couldn't find a good answer yet.. something like WHERE [Assigned To] = 'X' AND (([Assigned To] != 'X') asof '<30daysago>').
But this is still not a bulletproof solution.

有更好的想法吗?

谢谢&亲切的问候

Thanks & kind regards

西蒙

推荐答案

这似乎无法仅使用 WIQL，但您可以接近.

It appears that this is not possible using just WIQL, but you can get close.

关键字 @Today 将给出今天的日期，然后从中减去您的范围.应用到 [Status]='AssignedTo' 的 EVER 关键字以及将过去 30 天的日期与 [StateChangeDate] 进行比较是您需要完成的操作.

The keyword @Today will give you today's date, then just subtract your range from it. The EVER keyword applied to [Status]='AssignedTo' and a comparison against a date 30 days in the past to [StateChangeDate] is what you'll need to accomplish this.

尽可能接近 WIQL 和现有字段:
这表示，从所有修订(状态更改)返回用户X"曾经被分配给并且状态在过去 30 天内发生更改的记录.这基本上会让您对您的用户在上个月所做的工作有一个稍微模糊的了解.

As close as you can get with WIQL and existing fields:
This says, from all revisions (status changes) return records where the user 'X' has ever been AssignedTo and the State has changed in the last 30 days. This will basically give you a slightly fuzzy picture of what your User has been working on in the last month.

WHERE [Microsoft.VSTS.Common.StateChangeDate] >= @today - 30  
  AND  [System.AssignedTo] EVER 'Bennett Aaron' 
    ORDER BY [System.State]

添加缺少的字段:
您可以添加一个名为 AssignedDate 的自定义字段，该字段是在您在 Work Item Definition XML 中创建的 New->AssignedTo 工作流转换期间捕获的.您可以使用 Visual Studio 的 Team Foundation Server Power Tools 扩展来完成此操作.这将为您提供您所需要的内容以及未来的其他报告选项.

Add the missing field:
You could add a custom field called AssignedDate that is captured during the New->AssignedTo workflow transition that you create in the Work Item Definition XML. You can accomplish this using the Team Foundation Server Power Tools extension to Visual Studio. This would give you exactly what you need as well as additional reporting options going forward.

TFS API
我无法帮助您解决这个问题，但我相信您可以使用 TFS API 进行查询.

TFS API
I cannot help you with this one, but I believe you could query using the TFS API.

我经历过的一些快速问题可以节省您在 ASOF 和 EVER 上的时间:

A couple of quick gotchas I've experienced to save you time on ASOF and EVER:

AsOf 本身不会帮助您解决这个问题，因为它不支持日期范围.它允许您像查询另一个日期一样进行查询.换句话说，如果您昨天忘记捕获查询的结果，您可以使用 AsOf 查询来获取昨天运行时会得到的结果.我的理解是你想查询一个基本的日期范围.

AsOf won't help you by itself with this as it does not support a range of dates. It allows you to query as if it were another date. In other words, if you forgot to capture the results of a query yesterday, you can use an AsOf query to get the results that you would have gotten had it run yesterday. What I understand is that you want to query a basic date range.

EVER 可能无法按照您对日期的预期工作，因为我相信它使用它测试的字段的确切值(将包括日期字段的时间戳部分).只需确保针对状态字段而不是日期使用 EVER 关键字.

EVER might not work as you expect against dates as I believe it uses the exact value of the field (timestamp portion of the date field would be included) it tests with. Just make sure the EVER keyword is used against the status field rather than a date.

这篇关于查找过去 30 天内分配给 X 的工作项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！