title: SQL Query for JIRA issues
body: <p>I have to get some data from JIRA database and create a view to show them.</p>
  
  <p>In our JIRA, we have around 100 projects. We need to create a Oracle view to visualize following data.</p>
  
  <p>Our work-flow has following statuses
      Open
      Fix
      Resolved
      Test
      Validate
      Closed
      Rejected</p>
  
  <pre><code>For a single project, if issues are Delivery-CR, Fault-Bug, Fault-Specification, Fault-Configuration or Fault-Testing we need to create a view to visualize following data.
  
  •   transition date – open to fix
  •   transition date – fix to resolved
  •   transition date – resolved to test
  •   transition date – test to validate
  •   Issue type
  •   Status 
  •   Fix version
  
  Note that Issue type, Status and Fix version are on CHANGEITEM table. I can't find a table to get transaction dates. 
  
  We are using JIRA 6.3.7 and Oracle 11g database
  </code></pre>
  
  <p>I have tried with following query but no success.</p>
  
  <pre><code>select created, issueid, ci.oldstring, ci.newstring, ci.oldvalue, ci.newvalue from changegroup inner join changeitem ci on changegroup.ID = ci.groupid where issueid in (select ID from JIRAISSUE where PROJECT in (select ID from PROJECT where PNAME = 'Singapore Exchange Post Trade') and ISSUETYPE in (select ID from ISSUETYPE where PNAME in ('Delivery-CR', 'Fault-Bug', 'Fault-Specification', 'Fault-Configuration', 'Fault-Testing'))) where ci.oldstring in('Open','Fix','Resolved','Test','Validate','Closed','Rejected');
  </code></pre>
  
  <p>Can some one tell me where I have gone wrong.</p>