13

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.

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.

Any better ideas?

Thanks & kind regards

Simon

Simon Woker
  • 4,994
  • 1
  • 27
  • 41

1 Answers1

14

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

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.

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]

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
I cannot help you with this one, but I believe you could query using the TFS API.



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

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 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.

Aaron Bennett
  • 3,904
  • 1
  • 17
  • 8
  • Thanks a lot. I hoped it would be possible to query the history somehow. An extra field is way too much, it would just be a "nice to have" feature to my program. Maybe it COULD be possible using something like this, but I guess it's just total overkill :) `(([Assigned To] != 'X') asof '<30daysago>') AND (([Assigned To] != 'X') asof '<29daysago>') ...` – Simon Woker Mar 03 '11 at 19:53
  • Sorry about that, went to lunch before I could update this! Just added your answer. I was doing some testing against the EVER clause that might work for you. I don't have much data to test against, so I would love to know what the criteria I have returns for you – Aaron Bennett Mar 03 '11 at 19:56
  • No problem! Unfortunately I get all WorkItems that were changed in the last 30 days and were ever assigned to me. So I tried 2 other queries which won't even parse... I cannot use NOT and EVER in combination, cannot use ASOF for a single satement and cannot use [System.ChangedDate] in the where-clause first `WHERE ([Assigned To] = 'X') AND [System.ChangedDate] >= @Today-2 AND NOT ([Assigned To] EVER 'X')"` and second `WHERE ([Assigned To] = 'X') AND [System.ChangedDate] >= @Today-2 AND ([Assigned To] != 'X' ASOF [System.ChangedDate])` Seems it's just not possible to do this with WIQL – Simon Woker Mar 04 '11 at 09:56