we have a challenge - select JIRA worklogs with their Tempo approval status from JIRA database. I have found how to get WL attributes with values, but this does not contain approval status. The approval status is contained in the table AO_86ED1B_TIMESHEET_APPROVAL, but I am not able to find some relation between JIRA worklog / timesheet approval. What I need is a simple SQL select with result like this:
user, worklog id, worklog status (approved/not approved).
Then I will be able to select any other related data like issue customfields etc. but it is not the question now.
We have Tempo Timesheets 10, database SQL Server 2016, JIRA 8.1.
We cannot use Tempo REST API (because of loading data to PowerBI and they demand SQL select rather than REST API due to performance issues and clear architecture blueprint ;))
Thanks in advance
You can use the following query
Select top 1000 *, FORMAT(DATEADD(DD,-(DATEPART(WEEKDAY, wl.startdate)+5) % 7, wl.startdate), 'ddMMyyyy') as period, case when ta.id is not null then 'Approved/submitted' else 'Not approved/not submitted' end from worklog wl
select max(id) as id, user_key, period from [dbo].[AO_86ED1B_TIMESHEET_APPROVAL] group by user_key, period
) ta on ta.user_key = wl.author and FORMAT(DATEADD(DD,-(DATEPART(WEEKDAY, wl.startdate)+5) % 7, wl.startdate), 'ddMMyyyy') = ta.period
where wl.startdate between '2020-08-01' and '2020-08-31' order by wl.startdate desc
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event