SQL - Useful Queries

Created by Martin Parkinson, Modified on Wed, 24 Jan 2024 at 04:25 PM by Graeme Orchard

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)


TABLE OF CONTENTS





1) 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


Back to top





2) 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


Back to top





3) 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


Back to top





4) 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


Back to top



5) 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.


Query Text

SELECT CAND_POSITION, COUNT (CAND_UNIQ_ID) as'Qty', SUM(CAND_AGE) as'TotalYrs' FROM CANDTAB0001
Where CAND_AGE >'0'
 GROUP BY CAND_POSITION


Example Output



6) List Candidate Attributes

The SQL query text below can be pasted into the SQL Query tool.

It will list candidates And display their Uniq_ID, Reference No, Forename, Surname plus any Attributes, plus the 'LEVEL' (1-5) and whether they are TICKED, Suggested or Marked as No  (✅  ▶️ or❌ )


Query Text

SELECT CANDTAB0001.CAND_uniq_id, CANDTAB0001.CAND_ref, CANDTAB0001.CAND_FORENAME, CANDTAB0001.CAND_SURNAME, ATTDEF0001.ATDF_DESC, ATXF_LEVEL, ATXF_CONFIRMED_YND, ATXF_PAT_COUNT, ATXF_SOURCE, ATXF_QUAL_DATA1 FROM ATODBXF0001, CANDTAB0001, ATTDEF0001 

where ATODBXF0001.ATXF_OBJ_UNIQ = CANDTAB0001.CAND_UNIQ_ID and ATODBXF0001.ATXF_ATT_UNIQ = ATTDEF0001.ATDF_UNIQ and CANDTAB0001.CAND_UNIQ_ID <> 0 AND ATODBXF0001.ATXF_SOURCE <>'T' AND ATODBXF0001.ATXF_OBJ_TYPE ='CAND' 

order by CANDTAB0001.CAND_uniq_id, ATODBXF0001.ATXF_DISP_ATT_CODE



EXAMPLE OUTPUT


In the example above, the Confirmation column shows whether the item is Ticked/Suggested/Crossed

 ✅ = Y   (Suggested) ▶️  = C       No/Rejected ❌ = N ) 


IMPORTANTWhen using the above query, it looks at the ODBC Attributes File.
This file needs to be REBUILT in order to show accurate/up-to-date results.

This can currently be done MANUALLY from:
Maintenance > Tools > Attributes Toolkit   Once in there, click the [OBDC] button to rebuild the file.






Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article