nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to retrive Sprint data from Database

Venkata Sagar Ganesh Rao Mahendrakar

Can we retrieve Sprint Data from Database.?

May i know how can i retrieve.

Thanks

Sagar

Accepted answers

All comments

Trudy P Claspill

Hello @Venkata Sagar Ganesh Rao Mahendrakar

What information about the sprint are you trying to get?

Venkata Sagar Ganesh Rao Mahendrakar

Hi @Trudy P Claspill

Sprint Name

Start Date

End Date

Completed Date

Completed Sprints

Active Sprints

Thanks

Trudy P Claspill

See if this article answers your questions.

https://confluence.atlassian.com/jirakb/determining-sprint-status-from-the-database-955177463.html

Venkata Sagar Ganesh Rao Mahendrakar

Hi @Trudy P Claspill ,

Thanks for the article!!

But is it possible to see the relation of this table with the jira issue table ?

The end goal is to get the jira ID and the sprint name associated with it.

Thanks

Sagar

Trudy P Claspill

Please clarify the problem you are trying to solve and your requirements.

Your original question was about getting sprint data only. You didn't say anything about wanting issue data.

Why do you need to get the information from the database? There are Sorint reports within the UI to show the Sprint information plus the issues in the sprint.

Anudeep Gudipelli

Hi Venkata,

Below Query might provide you with the results you are looking for,

select

jp.projecttype AS "Project Type",

jp.pname AS "Project Name",

jp.description as "Project Description",

cu_lead.display_name as "Project Lead",

jp.pkey AS "Project Key",

ji.id AS "Issue ID",

(jp.pkey || '-' || ji.issuenum) AS "Issue Key",

STRING_AGG(DISTINCT aos."NAME", ', ') AS "Sprint Names", -- Added Sprint Names

STRING_AGG(DISTINCT

CASE

WHEN aos."STARTED" = TRUE AND aos."CLOSED" = FALSE THEN 'Active'

WHEN aos."STARTED" = FALSE AND aos."CLOSED" = FALSE THEN 'Future'

when aos."STARTED" = TRUE AND aos."CLOSED" = TRUE THEN 'Completed'

ELSE NULL

end,',') AS "Sprint Statuses", -- Added Sprint Status

sprint_info."Sprint Information" -- Added Sprint Information

FROM project jp

JOIN jiraissue ji ON jp.id = ji.project

JOIN issuetype i ON ji.issuetype = i.id

left join app_user au_lead on jp.lead = au_lead.user_key

-- Join for Project Lead

left join cwd_user cu_lead on au_lead.lower_user_name = cu_lead.user_name

LEFT JOIN app_user aur ON ji.reporter = aur.user_key

LEFT JOIN app_user aua ON ji.assignee = aua.user_key

-- Join for sprints

LEFT JOIN ( SELECT distinct cfv.issue, cfv.stringvalue AS sprint_id from customfieldvalue cfv

where cfv.customfield = (SELECT id FROM customfield WHERE cfname = 'Sprint') ) distinct_sprints ON distinct_sprints.issue = ji.id

--LEFT join customfield cf_sprint ON cf_sprint.cfname = 'Sprint'

--LEFT join customfieldvalue cfv_sprint ON cfv_sprint.issue = ji.id AND cfv_sprint.customfield = cf_sprint.id

LEFT JOIN "AO_60DB71_SPRINT" aos ON aos."ID" = CAST(distinct_sprints.sprint_id AS INTEGER)

-- Joins Sprint Information

LEFT JOIN (

SELECT

cfv_sprint.issue,

STRING_AGG(DISTINCT CASE

WHEN aos."STARTED" = TRUE AND aos."CLOSED" = FALSE THEN aos."NAME" || ' ends ' || TO_CHAR(TIMESTAMP 'epoch' + aos."END_DATE" / 1000 * INTERVAL '1 second', 'DD/Mon/YYYY')

WHEN aos."STARTED" = FALSE AND aos."CLOSED" = FALSE THEN aos."NAME" || ' Starts on ' || TO_CHAR(TIMESTAMP 'epoch' + aos."START_DATE" / 1000 * INTERVAL '1 second', 'DD/Mon/YYYY') || ' Ends on ' || TO_CHAR(TIMESTAMP 'epoch' + aos."END_DATE" / 1000 * INTERVAL '1 second', 'DD/Mon/YYYY')

WHEN aos."STARTED" = TRUE AND aos."CLOSED" = TRUE THEN aos."NAME" || ' Completed on ' || TO_CHAR(TIMESTAMP 'epoch' + aos."COMPLETE_DATE" / 1000 * INTERVAL '1 second', 'DD/Mon/YYYY')

ELSE NULL

END, ', ') AS "Sprint Information"

FROM

customfieldvalue cfv_sprint

LEFT JOIN "AO_60DB71_SPRINT" aos ON aos."ID" = CAST(cfv_sprint.stringvalue AS INTEGER)

WHERE

cfv_sprint.customfield = 10300 -- Replace with your actual Sprint custom field ID

GROUP BY

cfv_sprint.issue

) sprint_info ON sprint_info.issue = ji.id

where jp.pkey IN ( 'CTIOPA')

group by

ji.id, jp.projecttype, jp.pname, jp.description, cu_lead.display_name, jp.pkey, ji.issuenum, sprint_info."Sprint Information";

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

data-center

jira-software-data-center