Tempo worklogs - how to get them from database with approval attribute

[Vitek Urban]

Hi,

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

Vitek

[Sajan Kumar Devarakonda · Accepted solution]

Hi Vitek,

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
Left join 
(
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