Hey all!
I'm using Jira data replicated into my warehouse, and I'm wondering how could I manage to filter the data in the issues table to only get the issues in the currently active sprint. It's required for some reporting, and I'd like to make it dynamically to always target current sprint, and drop the tickets either in the backlog or past sprints.
Thanks a lot!
This involves some reverse engineering to understand the Jira Software AO tables. I think I would start by using REST at
GET /rest/agile/1.0/board/{boardId}/sprint
which returns a list of sprints, find the ID of the active sprint and then use
GET /rest/agile/1.0/sprint/{sprintId}/issue
to get the issues. Once that was working I would jump into the source code for the REST API for Jira Software and see what tables are being used.
Alternatively poke around a test instance (not warehouse) and the AO_60DB71 tables. Then take that info to the warehouse tables.
Fiddly!
Perhaps start with
AO_60DB71_SPRINT
but I'm not sure in the "started" field is the same as "active"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for the reply Matt!
I'll try some of what you mentioned, but we're replicating the tables via Stitch, so there's not much I can do on the replication side. That's why I was facing a SQL approach, with the data already loaded.
Thanks again!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for the reply!
Yes, I've already checked those, but I have no table named ao_60db71_sprint, so that's why I'm asking.
I thought I could get it by combining issues table and one I found with the sprints data (including a status column), but not working:
SELECT * FROM jira.issues
WHERE id IN(
SELECT _sdc_source_key_id FROM jira.issues__fields__customfield_10007
WHERE boardid = 207 --specific board I'd like to pull data from
AND state = 'active');
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @jfreidkes - Welcome to the Atlassian Community!
If you are filtering within JQL it would look like:
project = ABC and sprint in openSprints ()
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Was afraid of that. Not sure how you are denoting current sprints when you replicate the data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.