Hi, I'm wondering if it's possible in AA to create a SQL query that achieves the same thing for the JQL statement of Assignee WAS X DURING (Date1, Date2)
Hello! You could use a SQL query like the below code to achieve similar results as your JQL filter. You'd just need to replace the 'Account_ID' with the account ID of your desired assignee and change the dates to your desired date range.
SELECT `Issue`.`assignee_account_id` AS `Assignee account ID`FROM `jira_issue` AS `Issue`WHERE (`Issue`.`assignee_account_id` = 'Account_ID')AND `Issue`.`created_at` >= TIMESTAMP('2024-11-01')AND `Issue`.`created_at` < (TIMESTAMP('2024-11-07') + INTERVAL 1 DAY)GROUP BY `Issue`.`assignee_account_id`ORDER BY `Issue`.`assignee_account_id` ASCLIMIT 1000;
Hi Jessie, thanks for the reply, doesn't that query only show issues that are assigned to someone based on the creation date? The key part of the JQL is the WAS condition, so I'm not interested in knowing which issues someone is currently assigned to, but issues they also were assigned to during a specific timeframe
If you're looking to query the issues a user was previously assigned to during your timeframe, you can use the Issue history table instead of the Issue table. The Issue history table holds the history of the issues and will have data on whether the assignee of a ticket changes.
You can also change the date filtering in the WHERE clause to use the "Started at" column from the Issue history table instead of the issue created date. The "Started at" date will be when the change began, such as when the assignee changed from user 1 to user 2.
I hope that helps!
That's great, thanks! What about if the assignee doesn't change? i.e., the assignee is set on issue creation?
It looks like you're new here. Sign in or register to get started.
