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

Vitek Urban June 7, 2019

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

1 answer

1 accepted

1 vote
Answer accepted
Sajan Kumar Devarakonda August 26, 2020

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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events