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
select p.pkey,j.issuenum,w.author,w.CREATED, (w.timeworked/60/60) as "Log work"from project pinner join jiraissue j on p.id=j.projectinner 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.
Why would you want to use SQL instead of JQL?
strictly speaking this would get the jql results
"my field" = X and worklogDate > '1970-01-01'
or: timespent is not empty
or a combination of worklogDate, worklogAuthor, and so forth
thanks, I need the SQL querry because I use Power BI to get the data out of the database
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.
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
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.
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)
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.
It looks like you're new here. Sign in or register to get started.
