A new reporting tool has been added to the system after release 1.00.17.052
It allows users to write SQL queries and return results from the database. These results can then be exported to MS excel for further analysis or manipulation. To find out more on SQL's Click Here.
Below are some useful SQL Queries that you may want to add onto your database.
Contents (Click an Item to Jump to that section)
Count Booking Header Status Change
Count Cancelled Bookings by User
Find Candidates by Booking Ref
Find Candidates Average Age for Role
Count Booking Header Status change
The SQL query below can be used to count how many 'Booking Header' status change records have been created by each user in a given date range.
Simply cut/paste the query text below into the SQL tool, and give the query a NAME so that you can save it.
Query Text
SELECT JNLS_CREATED_BY, JNLS_OBJ_TYPE, COUNT(JNLS_CREATED_BY) as'Qty' FROM JNLSTAT0001
Where JNLS_CREAT_DATE>='2020-01-01' and JNLS_CREAT_DATE<='2020-01-31' and JNLS_OBJ_TYPE='BKHD'
GROUP BY JNLS_CREATED_BY
Example Output
Count Candidates by Status
The SQL query below can be used to count how many candidates there are at each Status.
Simply cut/paste the query text below into the SQL tool, and give the query a NAME so that you can save it.
Query Text
SELECT CAND_STATUS, COUNT(CAND_STATUS) as'Qty' FROM CANDTAB0001
GROUP BY CAND_STATUS
Example Output
Count Cancelled Bookings by User
The SQL query below can be used to count how many cancelled bookings exist from a particular date, and give a total per user.
Simply cut/paste the query text below into the SQL tool, and give the query a NAME so that you can save it.
Query Text
SELECT BKNG_BOOK_TYPE, BKNG_CREATED_BY, COUNT(BKNG_UNIQ_ID) as'Qty' FROM BKNGTAB0001
WHERE BKNG_DATE>'2019-06-01' and BKNG_BOOK_TYPE like 'CA%'
GROUP BY BKNG_CREATED_BY
NOTE: The query above assumes that your CANCELLATIONS use a booking type of CAN (or anything else starting CA.... )
When using the query you would need to amend the booking dates shown (e.g. '2019-06-01') to show the date from which you want to see any cancellations.
Example Output
Find Candidates by Booking Ref
The SQL query text below can be pasted into the SQL query tool.
Simply replace the 'BOOKING_ID' selection field at the end, with the booking ID you are searching for.
Query Text
SELECT BKNG_DATE, BKNG_BOOK_TYPE, BKNG_BOOKING_ID, BKNG_BRANCH, CAND_FORENAME, CAND_SURNAME, CAND_REF, CAND_STATUS, CAND_UNIQ_ID, CAND_BRANCH_ACCESS001 FROM BKNGTAB0001, CANDTAB0001
where CANDTAB0001.CAND_UNIQ_ID=BKNGTAB0001.BKNG_CAND_UNIQ and BKNGTAB0001.BKNG_BOOKING_ID='1000245720'
Example Output
Find Candidates average Age
The SQL query text below can be pasted into the SQL Query tool.
It will produce a list of each role, and show the number of candidates (provided thier age is greater than 0) and the Total of their ages. This can be used to calculate the average age of candidate for each role.
SELECT CAND_POSITION, COUNT (CAND_UNIQ_ID) as'Qty', SUM(CAND_AGE) as'TotalYrs' FROM CANDTAB0001
Where CAND_AGE >'0'
GROUP BY CAND_POSITION
Lottie Coley
Comments