How to retrive Sprint data from Database

[Venkata Sagar Ganesh Rao Mahendrakar]

Hi

Can we retrieve Sprint Data from Database.? 

May i know how can i retrieve.

Thanks

Sagar

[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";   

---

    

[Trudy Claspill]

Hello @Venkata Sagar Ganesh Rao Mahendrakar 

What information about the sprint are you trying to get?

[Venkata Sagar Ganesh Rao Mahendrakar]

Hi @Trudy Claspill 

Sprint Name

Start Date 

End Date

Completed Date

Completed Sprints

Active Sprints

Thanks

[Trudy 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 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 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.

[Venkata Sagar Ganesh Rao Mahendrakar]

Hi Trundy

Thanks for the reply!!

The Main Goal is We wanted to retrieving data of Sprints along with Jira Issue id to Tableau Dashboard. 

Thanks

[Trudy Claspill]

This post from 2017 might help you get the issues related to sprints, from the database.

https://community.atlassian.com/t5/Jira-Software-questions/SQL-for-getting-all-issues-related-to-a-sprint/qaq-p/663162