need to generate report of all issues assigned to a user
Submitted by horstuff
on 2014-03-18
I need a report of all issues assigned to a specific user in the last year. This would include assigned to and then completed by. Can this be done? Thanks.
The 'changes' table in the database contains all historical values of attribute. The 'value_new' column would contain the user name (as text), 'attr_id' is the identifier of the Assigned To attribute, 'change_id' is a key to the 'stamps' table which contains the date and time stored as UNIX timestamp, and 'issue_id' is obviously the identifier of the issue.
Thanks Michal. I looked through those tables and it's not clear to me how to get what I need... is there any way you could write up the query? I need to know how many and which issues were assigned to a particular user in the last 365 days, and preferably sent by whom as well. Thank you very much for your help.
Side note... I don't know why (I'm not the db admin), but there are multiple versions of web issues tables in there. Some are called wi105b, some are just webissues, and some are called webissues 105, 105a, 105b, etc. up through 105e. Any ideas why that is?
SELECT i.issue_id, i.issue_name, st.stamp_time, u.user_name
FROM changes AS ch
INNER JOIN stamps AS st ON st.stamp_id = ch.change_id
INNER JOIN issues AS i ON i.issue_id = ch.issue_id
INNER JOIN users AS u ON u.user_id = st.user_id
WHERE ch.attr_id = 1 AND ch.value_new = N'John Doe' AND st.stamp_time >= 1363651200
Replace 1 with the actual Assigned To attribute ID and 'John Doe' with the user name. The value 1363651200 is the UNIX timestamp for 3/19/2013, 0:00 UTC.
The query returns the issue ID and name, time when the issue was assigned to John Doe, and the name of the user who assigned it.
I suspect that the prefixes like 105, 105a etc. were used to test various versions of WebIssues (for example 1.0.5), but you'd have to ask your DB admin :).
Actually, 3 more quick questions... I am running it without the attr_id criteria, but I AM showing the attr_id in the results just so I see it. That was the "1" in your query. I replaced it with the user id that matches the user name and got no results... that's why I took it out as a criteria. Question: the attr_id is not the user id? It showed "21" in the results all the way through, but this user has an id of "6".
Also, where would I get the description of the issue, not just the title?
Where do I get the data that shows who actually completed the task (not just who it was assigned to)?
No, attr_id is not the user id, it's the attribute ID (an attribute is Assigned To, Priority, Status, etc.). The value of an attribute is always stored as text, not as the user ID.
Descriptions are stored in the issue_descriptions table.
In order to see who completed the task you would have to check the changes table again, using the Status attribute and the appropriate value, for example 'Completed'.
The 'changes' table in the database contains all historical values of attribute. The 'value_new' column would contain the user name (as text), 'attr_id' is the identifier of the Assigned To attribute, 'change_id' is a key to the 'stamps' table which contains the date and time stored as UNIX timestamp, and 'issue_id' is obviously the identifier of the issue.
Regards,
Michał
Thanks Michal. I looked through those tables and it's not clear to me how to get what I need... is there any way you could write up the query? I need to know how many and which issues were assigned to a particular user in the last 365 days, and preferably sent by whom as well. Thank you very much for your help.
Side note... I don't know why (I'm not the db admin), but there are multiple versions of web issues tables in there. Some are called wi105b, some are just webissues, and some are called webissues 105, 105a, 105b, etc. up through 105e. Any ideas why that is?
The query would like like this:
Replace 1 with the actual Assigned To attribute ID and 'John Doe' with the user name. The value 1363651200 is the UNIX timestamp for 3/19/2013, 0:00 UTC.
The query returns the issue ID and name, time when the issue was assigned to John Doe, and the name of the user who assigned it.
I suspect that the prefixes like 105, 105a etc. were used to test various versions of WebIssues (for example 1.0.5), but you'd have to ask your DB admin :).
Regards,
Michał
I tried that but got an error. I have made a screenshot of the error... is there a way to attach a file to these posts?
url of the screenshot: http://nowza.com/WIquery.gif
That's in MS Access. I changed the table names but have made triple sure all is correct.
It looks like with Access you have to add some parentheses to make the JOINs work, see http://stackoverflow.com/questions/19367565/sql-inner-join-wih-multiple-table. Why are you using Access with WI anyway? :)
Regards,
Michał
Got it working. Access is just the GUI I use to deal with queries. Thanks much for the help, great product.
Actually, 3 more quick questions... I am running it without the attr_id criteria, but I AM showing the attr_id in the results just so I see it. That was the "1" in your query. I replaced it with the user id that matches the user name and got no results... that's why I took it out as a criteria. Question: the attr_id is not the user id? It showed "21" in the results all the way through, but this user has an id of "6".
Also, where would I get the description of the issue, not just the title?
Where do I get the data that shows who actually completed the task (not just who it was assigned to)?
No, attr_id is not the user id, it's the attribute ID (an attribute is Assigned To, Priority, Status, etc.). The value of an attribute is always stored as text, not as the user ID.
Descriptions are stored in the issue_descriptions table.
In order to see who completed the task you would have to check the changes table again, using the Status attribute and the appropriate value, for example 'Completed'.
Regards,
Michał