SQL query to fetch Issues created in certain project between two given dates

[Rama krishna]

Hello!!

Can some one helped me in writing SQL query to fetch from DB directly for below JQL.

Project = "ABC" AND Issuetype in (Task,Story) AND createdDate >= "2019-09-01" and createdDate < "2019-10-01"

Thanks  in Advance!!

Regards

Ramakrishna

[fran garcia gomera]

select * from jiraissue.ji 
where ji.issuetype in 
(select id from issuetype where pname in ('Task','Story'))
and ji.project in
(select id from project where pname in ('ABC'))
-- or 
--(select id from project where pkey in ('ABC'))
-- if ABC is the project key instead of the project name
and CREATED > '2019-08-31'    -- to get 2019-09-01 included, CREATED is time/hour
and CREATED < '2019-10-01'

[RianS]

Here's a search some SQL you can try

For Postgres:

SELECT jiraissue.* FROM jiraissue
     INNER JOIN project ON jiraissue.project = project.id
     INNER JOIN issuetype ON jiraissue.issuetype = issuetype.id
WHERE
     project.pkey IN ('PROJECTKEY')
     AND issuetype.pname IN ('Task','Story')
     AND jiraissue.created >= '2019-09-01'
     AND jiraissue.created < '2019-10-01'
ORDER BY jiraissue.issuenum;

For Oracle:

SELECT jiraissue.* FROM jiraissue
INNER JOIN project ON jiraissue.project = project.id
INNER JOIN issuetype ON jiraissue.issuetype = issuetype.id
WHERE
project.pkey IN ('PROJECTKEY')
AND issuetype.pname IN ('Task','Story')
AND jiraissue.created >= to_date('2019-09-01', 'YYYY-MM-DD')
AND jiraissue.created < to_date('2019-10-01', 'YYYY-MM-DD')
ORDER BY jiraissue.issuenum;

This should give you the issues you are looking for.