Recreate JQL assignee WAS X DURING (Date1, Date2) in AA

[nclark]

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)

[Jessie Turpin]

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` ASC
LIMIT 1000; 

[nclark]

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

[Jessie Turpin]

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! 

[nclark]

That's great, thanks! What about if the assignee doesn't change? i.e., the assignee is set on issue creation?

[Jessie Turpin]

You're welcome! If the assignee hasn't changed from when the user was first assigned, then the initial assignee would be the only assignee value in the Issue history table for that specific ticket.