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
• 2) Count Candidates by Status
• 3) Count Cancelled Bookings by User
• 4) Find Candidates by Booking Ref
• 5) Find Candidates average Age
• 6) List Candidate Attributes
• 7) Find Duplicate Candidate email addresses
• 8) Find Duplicate Candidate phone numbers

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 ) 

7) Find Duplicate Candidate email addresses

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

It will list candidates with the same email address, and give a count.

Query Text

SELECT CAND_EMAIL, COUNT(*) FROM CANDTAB0001

GROUP BY CAND_EMAIL

ORDER BY COUNT(*) DESC

8) Find Duplicate Candidate phone numbers

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

It will list candidates with the same telephone number, and give  a count

Query Text

SELECT CAND_PHONE, COUNT(*) FROM CANDTAB0001

GROUP BY CAND_PHONE

ORDER BY COUNT(*) DESC

Attachments (5)

bN3Zzj2ke4Cq....png
13.1 KB

lDrJ8Sanpycl....png
12.8 KB

72tsITeIcJxW....png
32.9 KB

n7FI5foMXbf7....jpeg
112 KB

XlTM4XKaId3Z....png
12.6 KB