SQL querry for Issue information with Customfield and worklog?

[RMA]

Hi,

I want to make a database SQL querry to get the value of all issues which contain a specific custom field and which have logged work. Does anyone have an idea how to make that querry?

Best Regards

Robert

[KAGITHALA BABU ANVESH · Accepted solution]

select p.pkey,j.issuenum,w.author,w.CREATED, (w.timeworked/60/60) as "Log work"
from project p
inner join jiraissue j on p.id=j.project
inner join worklog w on w.issueid=j.id order by w.CREATED DESC;

This will gives you some information, but i don't have any idea about adding your custom field.

[Nic Brough -Adaptavist-]

I would question why you think you want to use SQL.

It's the worst, most painful, most miserable way you can possibly ask a question of a Jira database, and people usually get it wrong very quickly.  A simple human query like "show me the most simple issue you have" needs over 20 joins, and each field adds at least another 2.

What is the problem you are trying to solve?  There is going to be a better way to solve it than using SQL.

[RMA]

The requirement is that I need to provide JIRA Data in Power BI tool. So I have connected the JIRA SQL database with Power BI so far. But in Power BI I can see all the tables from JIRA. So the easiest way for me would be to make a SQL querry for the database connection that I get only the data I need. But if such a querry in the JIRA Database is so painful I might search for another solution.

[Nic Brough -Adaptavist-]

I would always recommend looking for a reporting tool that does the work for you.

If you're wedded to Powerbi (nothing wrong with that), then I'd use one of the tools that handles "understanding the database" for you - see https://marketplace.atlassian.com/search?query=powerbi for the list (I can happily recommend the two with the largest installation numbers - seen them both work well for several customers)

[RMA]

Thanks, but I have to use power BI as it is used in several departments in our company. The addons for power BI connection I also checked out. But I think it can't be that you need a third tool to connect tools like JIRA and Power BI. So I will have try with this querry first. 

[Nic Brough -Adaptavist-]

You'll quickly learn to hate the SQL you're going to have to build, plus all the fun of explaining to people why the reports they're generating are wrong.

[RMA]

Whatever I get paid for ;)

[KAGITHALA BABU ANVESH]

as you asked 

worklogDate >=2020-1-1

you can export the result with csv all fields 

after separation Log Work as = Log Work comment; Date and time when work logged ; His username , Log time in seconds

If you can separate data it also an easy format to export

[RMA]

thanks, I need the SQL querry because I use Power BI to get the data out of the database

[Radek Dostál]

Why would you want to use SQL instead of JQL?

[Radek Dostál]

strictly speaking this would get the jql results

"my field" = X and worklogDate > '1970-01-01'

[Radek Dostál]

or: timespent is not empty

or a combination of worklogDate, worklogAuthor, and so forth