Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

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
Community showcase
Published in Apps & Integrations

🍻🍂Apptoberfest Update: Upcoming Virtual Events 🎉

Hello Community! I hope you've been enjoying the 🍂Apptoberfestivities🍂 (I know I have!) The event is heating up next week with a series of virtual events that we're calling the 🍻🍂Partner App ...

619 views 7 23
Read article

Community Events

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

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you