Introduction
The SQL language is used to interrogate the OOH system database directly. This
allows administrators to retrieve any information required. The following
information provides various useful examples of using SQL to retrieve
information from the OOH system.
Find out what fields are available in a table
describe userdetail
This returns a list of the field names and details about the fields.
Back to top
Get the login ID for a particular user
select id, sname, fname from userdetail where sname = 'Smith'
This will return the login id, firstname and surname of any user with the
surname “Smith”.
Back to top
List log records for a particular user either logging in or
logging out
select * from log where user = 'clareb' and (event = 'Login_OK' or event = 'Logout')
The brackets “(” and “)” are important in this query as we want records which
have either [user = ‘clareb’ AND event = “Login_OK” ] or [user = ‘clareb’ AND
event = ‘Logout’].
The same result could be achieved with the query:
select * from log where user = 'clareb' and event in ('Login_OK','Logout')
Back to top
Find out who authorised a particular user
select * from log where event = 'Authorise' and details like '%louiseb%'
In the above query, we are looking for “Authorise” events, and we are also
looking for the UserID – but the UserID is held in the text of the details.
Using the word “like” allows a partial match and the “%” matches any sequence of
characters; in this case before or after the id we are looking for.
The query:
select * from log where event = 'Authorise' and details like '%alanwatt%' order by logid
returns a number of lines as the authority for this user was changed a number of
times – the output is sorted into the order the events occurred and therefore
each change to the authority can be tracked and identified.
Back to top
Create a mail list file for merging with a word processor
The following query will produce a mailmerge list of nurses on the system – by
using the CSV download option, the file can be saved on your local PC and used
to merge with Word or another word processor or used in a program such as Excel
or Access:
select users.id, title, fname, sname, add1, add2, town, county, postcode from users, userdetail where users.id = userdetail.id and users.permissions = 'S'
The above is a multi-line query (although it could be typed into a single line).
If, instead of nurses, a list of doctors is required, then the following query
could be used:
select users.id, title, fname, sname, add1, add2, town, county, postcode from users, userdetail where users.id = userdetail.id and users.permissions in ('U', 'O', 'G', 'D')
The permissions used on the system are as follows:
N - None U - Doctor O - Restricted Doctor G - Semi-restricted Doctor D - Director Doctor E - Training Doctor H - Registrar Doctor S - Nurse W - Nurse Practitioner J - Nurse Director A - Administrator T - Administrative assistant V - View Only B - Bulk Doctor R - Bulk Nurse C - Call Handler I - Driver X - Disabled
Back to top
Create a Telephone List (with email addresses)
The following query uses CONCAT to join several fields together in producing the
output:
select concat(title, " ", fname, " ", sname) as name, telephone, mobile, email from userdetail order by sname
Back to top
List all bookings made by a particular user on a particular day
The bookings table lists details of all bookings on the system. In addition to
the booking itself, the table holds information about who made the booking and
when it was made. So:
select * from bookings, users where bookings.booked_by = users.id and (users.permissions = 'T' or users.permissions = 'A') and users.id = 'steevh')
will return all bookings made by an administrator on behalf of the user “steevh”.
The “created” field holds the date and the time, so the query:
select * from bookings where created between '2004-12-29' and '2004-12-30'
lists bookings made from 00:00:00 on 29th December to 00:00:00 on the 30th
December or effectively all bookings actually made on the 29th December.
Back to top
When did a particular administrator log into the system on a
given day
The query:
select * from log where event = 'Login_OK' and user = 'clareb' and etime between '2004-12-20' and '2004-12-21'
Shows all logins made to the system by ‘cleareb’ on the 20th December. In this
case it is not possible to say:
select * from log where event = 'Login_OK' and user = 'clareb' and etime = '2004-12-20'
because the "etime" field specified a date and a time (it is actually
the number of seconds since 1st January 1970), and "etime =
'2004-12-20'" is the same as saying "etime = '2004-12-20
00:00:00'" which would only match log recors created at exactly midnight.
To circumvent this problem, a date is given instead.
Back to top
List new users awaiting authorisation
select concat(title, " ", fname, " ", sname) as name, created from userdetail, users where userdetail.id = users.id and users.permissions = 'N' order by created
The above query lists users who have not yet been given permission to use the
system. It is sorted into the order of the date that the user registered on the
system.
Back to top
List all shift sales
When a shift that is for sale is taken by another user, that event is logged to
the Event Log. The following query will list all events when a "For
Sale" shift is acquired by anothe user:
select * from log where event = 'Swapped'
Back to top
User Messages
Finding out when users confirmed acceptance of messages can be done using the
SQL command. When sending a message, the system assigns the message and ID
(mid), which you can later use to find out when the message has been read.
For instance, to see when each user has read message ID 10, you could use:
select * from user_messages where mid = '10'
If you wish to see the texts of past messages (perhaps to copy the text to use
again), and to retrieve the message ID's, use:
select * from messages
However, if you have sent alot of message on the system, it may be appropriate
to limit the number of messages returned by restricting the results to a range
of messages, using something like:
select * from messages where mid between 10 and 20
Back to top
GMC or Professional Insurance ExpiryMySQL allows
calculations on dates to be performed within the SQL command, and certain
functions can also be called. For instance, now() will return the date and time.
Using UNIX_TIMESTAMP converts the date and time to seconds since midnight on the
01/01/1970, which enables calculations on dates. The following will list
all GMC registrations which have either already expired or that will expire in
the next 60 days:
SELECT concat(title, " ", fname, " ", sname) as "Name",
gmc_no AS "GMC number",
DATE_FORMAT(gmc_expiry, "%d/%m/%Y") as "GMC Expiry"
FROM userdetail, users
WHERE users.id = userdetail.id
AND Permissions IN ('D', 'E', 'H', 'U','O')
AND UNIX_TIMESTAMP(gmc_expiry) <
(UNIX_TIMESTAMP(now()) + 60 * 60 * 60 * 24)
ORDER BY gmc_expiry
In the above, several functions have been used to either format the date or
to perform date calculations. 60 days is calculated as "60 * 60 * 60 *
24" seconds. A similar SQL command can be used to select Professional
Insurance Expiry:
SELECT concat(title, " ", fname, " ", sname) as "Name",
pi_name AS "Insurance Provider",
DATE_FORMAT(pi_expiry, "%d/%m/%Y") as "PI Expiry"
FROM userdetail, users
WHERE users.id = userdetail.id
AND Permissions IN ('D', 'E', 'H', 'U','O')
AND UNIX_TIMESTAMP(pi_expiry) <
(UNIX_TIMESTAMP(now()) + 60 * 60 * 60 * 24)
ORDER BY pi_expiry
More about Log Records
To display a list of event types, use:
select distinct event from log
Back to top
Further Reading
More information is available at:
MySQL Manual
Introduction to SQL
|