I am looking for a sql query which would do the following...

[neeraj gupta]

1. projects that have no issues stored inside them

2. creation date of first issue in a project - creation date of last issue in a project - time since the last issue was updated

Thanks,

Neeraj

[Radu Dumitriu]

1.

select p.pkey from project p where not exists (select id from jiraissue where project = p.id);

To see the count of issues per project:

select p.pkey, count(iss.*) from project p left outer join jiraissue iss on iss.project = p.id group by p.pkey order by 2 desc;

2. First issue created in the project and not deleted:

select min(created) from jiraissue where project = (select id from project where pkey='KEY');

Last issue (obviously):

select max(updated) from jiraissue where project = (select id from project where pkey='KEY');

For the time left, I think you can substract current time from the previous.

[neeraj gupta]

select p.pkey, count(iss.*) from project p left outer join jiraissue iss on iss.project = p.id group by p.pkey order by 2 desc;

the above query doesn't give the count of issues per project, infact it gives error

invalid user.table.column, table.column, or column specification.

Could you please cross check.

Thanks

[neeraj gupta]

if you can also let me know

number of scheduled reports i.e subscriptions

number of Filters in Jira

and number of report that run in a period of time.

Thanks

[Radu Dumitriu]

Dear Nerraj, these queries work in PostgreSQL quite fine. Thanks for reading your database manual. I will retag your original posting.

[neeraj gupta]

Dear Radu,

Home work done ;).

Thanks,

Neeraj