It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Find Statuses of all issues in a project on a given date in the past using Oracle

Karl Shlayfman Dec 03, 2012

I am trying to find a query that I can use from the backend to query issue counts for statuses on the given date in the past and so that it does not show it's current status but the status that it has back then.

2 answers

1 accepted

0 votes
Answer accepted
Clarissa Gauterio Atlassian Team Dec 03, 2012

Hi Karl,

I believe you can use the following query to achieve that:

SELECT JI.pkey, STEP.STEP_ID
FROM (SELECT STEP_ID, ENTRY_ID
      FROM OS_CURRENTSTEP
      WHERE OS_CURRENTSTEP.START_DATE < '<your date>'
UNION SELECT STEP_ID, ENTRY_ID
      FROM OS_HISTORYSTEP
      WHERE OS_HISTORYSTEP.START_DATE < '<your date>'
      AND OS_HISTORYSTEP.FINISH_DATE > '<your date>' ) As STEP,
(SELECT changeitem.OLDVALUE AS VAL, changegroup.ISSUEID AS ISSID
      FROM changegroup, changeitem
      WHERE changeitem.FIELD = 'Workflow'
      AND changeitem.GROUPID = changegroup.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
      FROM jiraissue) As VALID,
jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project = <proj_id>;

This query is taken from the following documentation, where you have additional examples that might be helpful as well:

Example SQL Queries for JIRA

Hope it helps!


Cheers,

Clarissa.

Karl Shlayfman Dec 03, 2012

do you know how i get status name and not number like this query provides?

Abeer Ishtiaq Sep 21, 2015

I have read this response on several sites. It returns the step ID which can be linked back to issuestatus.sequence column. However, that is not correct information. The issues are always returning between step ID 1 and 5 which map to default statuses. However, I have custom statuses and I want to see all the issues that were in that status on a certain date but cannot find them.

0 votes
Clarissa Gauterio Atlassian Team Dec 03, 2012

You can use the issuestatus table on the DB. This table links the status name and status ID.

Cheers,

Clarissa.

Karl Shlayfman Dec 03, 2012

I am sorry I think i just got a bit confused. It returns me step id which does not match status from the issuestatus table. is there a step id description table? Thanks

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Jira

Try Jira Cloud for Outlook: Organize your work without leaving your inbox

Hi Atlassian community, My name is Max and I work on the product integration team at Atlassian. I am pleased to announce the early access program for the Jira Cloud add-in for Outlook. This add-in...

2,399 views 6 15
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you