SQL: Last closed month

Robin January 14, 2021

Hi,

we have got a direct connection to the database (SQL). At the start of every month we close the last month for time logging. For our report I need the information if the last month is closed or not. Where do I get the information? In which table is the

 

Kind regards

Robin

 

 

3 answers

1 accepted

2 votes
Answer accepted
Susanne Götz _Tempo_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 14, 2021

Hi @Robin ,

In case you are using Tempo Timesheets to close periods, the information is stored in the  AO_86ED1B_PERIOD table. 
It is possible to retrieve the status of a Period via a (private) REST API, f.x. /rest/tempo-timesheets/3/period?key=1220 

A list of all DB tables that are provided by Add-ons can be seen in JIRA Administration, System, Plugin Data Storage (dependent on your JIRA version, there might be a problem with the display of this information, https://jira.atlassian.com/browse/JRASERVER-71733).

Best regards,
Susanne Götz
Tempo team

Robin January 14, 2021

Thank you,

 

yes that is the table I was looking for. Some further question:

 

1. The key is build lieke MMYY?

2. And if I find an entry the month is closed?

 

Kind regards

Robin

Susanne Götz _Tempo_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 14, 2021

Hi @Robin ,

The key is in the format MMYY (as it is listed in "Period Number" in Tempo Settings,  Period Management)

When you find an entry, it means that the period has been edited at some time, but not necessarily that it is closed. 

It might also be open for approvers and closed for team members. 
Adding here an example how the response can look like:
{
"id": 2,
"key": "0120",
"startDate": "2020-12-01",
"endDate": "2020-12-31",
"openForApprover": true,
"openForTeamMember": false,
"periodConfiguration": {
"id": 1,
"startDay": 1,
"startMonth": 1,
"defaultPeriod": true
}
}

Robin January 14, 2021

Thanks a lot.

 

This is the information I need. 

0 votes
Robin January 14, 2021

Hi,

thank you very much for the fast answer. Yes SQl is a terible way ;). But we have got several databases and need to combine the data.

Yes I can confirm, we are using the "Tempo" timesheet. Close means: After closing the month no one is able to log time in the "Tempo" sheet.

 

Kind regards

Robin 

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 14, 2021

SQL  is a terribe way to try to report on Jira data, the database is almost as far away as it can be set up for reporting and most of the queries you'll want to make are torturous and slow, as well as, in some cases, simply not there.  Some of the stuff you see on screen is not in the database at all.

Ignoring that and assuming you're happy with painful SQL, I'm afraid you have not given us enough detail to be able to help you find the tables you might need.

How are you "closing" a month?  I would guess you are using Tempo timesheets - can you confirm that?  Or tell us if you are using another app for timesheets, or explain what "close" means in plain Jira?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 14, 2021

Thank you @Susanne Götz _Tempo_  for saving me have to look up the tables, and explaining it better than I could!

Susanne Götz _Tempo_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 14, 2021

@Nic Brough -Adaptavist- , 

You're welcome :)

Suggest an answer

Log in or Sign up to answer