JIRA DB query to get status of all issues on a previous date

Pushparaj BS

select newvalue,issueid,created from jiraschema.changegroup,jiraschema.changeitem
where changegroup.created < '02/05/2013' AND
changeitem.GROUPID = changegroup.ID
)as CH1
left outer join (
select newvalue,issueid,created from jiraschema.changegroup,jiraschema.changeitem
where changegroup.created < '02/05/2013' AND
changeitem.GROUPID = changegroup.ID
) as
 CH2
on (CH1.issueid = CH2.issueid and CH1.created < CH2.created)
where CH2.issueid is null

This is what I'm using finding the max created date from changegroup table from the given date and getting the newvalue . THis gives some results , but missses out on issues which are created and no activity has been done as yet . Those will not be listed in this table and th eonly entry I find is in the assignee field with the new value as the username for the user .

cgauterio

Hi Pushparaj,

I believe you can use the following query to help you 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 retrieves the status of all issues on a give date in a give project. You can find more query examples at: Example SQL Queries for JIRA

Hope this helps!

Cheers,

Clarissa.