SQL queries

Field information for all tables in use

The following query provides the table name, field, data type, key type (primary, multiple, etc.), and description for all table in use (containing data):

SQL query

SELECT TABLE_NAME AS table_name, COLUMN_NAME AS field,
              COLUMN_TYPE AS data_type, COLUMN_KEY AS key_type, COLUMN_COMMENT AS description
FROM information_schema.columns
WHERE TABLE_NAME IN (
              SELECT TABLE_NAME
              FROM information_schema.tables
              WHERE TABLE_ROWS > 0
                             AND TABLE_NAME NOT LIKE '\_%'
        AND TABLE_NAME != 'proc')
LIMIT 9999;

Example table

Field Information for the Actor Table

This query provides the same information but returns it for one table that you have specified:

SQL query

SELECT TABLE_NAME AS table_name, COLUMN_NAME AS field,
              COLUMN_TYPE AS data_type, COLUMN_KEY AS key_type, COLUMN_COMMENT AS description
FROM information_schema.columns
WHERE TABLE_NAME = ’<$table_name>’
LIMIT 9999;

Instructions

• Replace <$table_name> with the table to be inspected.

Example table

SQL query

select
distinct(t.kode) 'Material Code',
t.omschr 'Material',
count(CASE WHEN l.aktie =1 THEN aktie END) 'Total number of checkouts'
from tabelitems t inner join loghfdnu l on t.kode = l.materiaal AND soort='RMTSYS' and l.aktie_datum BETWEEN DATE '<$startdate>' AND CURRENT_DATE
GROUP BY omschr
limit 9999

Instructions

• Replace <$startdate> with the desired Start Date for the report.
• (Optional) Replace CURRENT_DATE with the desired End Date for the report.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

Checkout by Material Type example report

SQL query

select t.kode 'Material Code', t.omschr 'Material', count(CASE WHEN l.aktie =1 THEN aktie END) 'Total number of checkouts', concat((Count(CASE WHEN l.aktie =1 THEN aktie END)* 100 / (select count(*) from loghfdnu where aktie=1 and aktie_datum BETWEEN DATE '<$startdate>' AND '<$enddate>')), '%') 'Percentage of Checkouts'
from 
tabelitems t inner join loghfdnu l 
on t.kode = l.materiaal 
AND soort='RMTSYS' 
AND l.aktie_datum BETWEEN DATE '<$startdate>' AND '<$enddate>'
GROUP BY omschr
limit 9999

Instructions

• Replace <$startdate> with the Start Date of the month.
• Replace <$enddate> with the End Date of the month.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

Checkout Change by Material Type example report

SQL query

select
distinct(t.kode) 'Material Code',
t.omschr 'Material',
l.eigenaar 'Placement branch',
l.oorsprong 'Owner branch',
(select count(distinct(t2.omschr)) from tabelitems t2 where t2.kode = t.kode) 'Number of Items',
count(CASE WHEN l.aktie =1 THEN aktie END) 'Total number of checkouts'
from tabelitems t, loghfdnu l where t.kode = l.materiaal and t.soort='RMTSYS' and l.aktie_datum BETWEEN DATE '<$startdate>' AND '<$enddate>'
GROUP BY omschr
limit 9999

Instructions

• Replace <$startdate> with the Start Date of the period.
• Replace <$enddate> with the End Date of the period.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

Collection Size - System example report

SQL query

select func_bx_naam_actor_utf8(ade.actor_id, 'A') as patronName, aam.auth_data,  ade.recno  from actor_div_export ade, actor_auth_middel aam where ade.actor_id = aam.actor_id and aam.auth_type='PAS' and ade.actor_id in 
       (select actor_id from (select actor_id, count(actor_id) as count, adm_status from (select actor_id, adm_status, count(adm_status) from fin_posten group by actor_id, adm_status) as groupbyactorandstatus group by actor_id having count=1) groupbyactorid where adm_status='*')
limit 9999

Collection Agency Check example report

SQL query

select rol as Role, COUNT(actor_id) as 'Count of Customers'
from rol where DATE(laatst_gebruikt) BETWEEN '<$startdate>'AND '<$enddate>'
GROUP BY rol
limit 9999

Instructions

• Replace <$startdate> with the Start Date of the month.
• Replace <$enddate> with the End Date of the month.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

Patrons Transacting example report

SQL query

select ab_nr 'Membership Code', omschrijving as 'Membership Type', COUNT(r.actor_id) as 'Count of Customers'
from rol r
LEFT JOIN rol_abonnement ra
ON r.ext_id = ra.ra_id
LEFT JOIN (
    SELECT DISTINCT ab_nr, instantie_id, omschrijving
    FROM abonnement_def) def
USING (ab_nr, instantie_id)
where DATE(laatst_gebruikt) BETWEEN '2022-08-01'AND '2022-08-31'
GROUP BY omschrijving
limit 9999;

Patrons Transacting based on Membership Type example report

SQL query

select a.voornamen 'First Name', a.naam 'Last Name', e.e_adres 'Phone Number' from actor a, actor_e_adres e where e.soort='TEL1' and e.rol='ABON' and (replace(e.e_adres, ' ', '') like '%<$last4digits>') and a.actor_id=e.actor_id
limit 9999

Instructions

• Replace <$last4digits> with the last four digits of the patron's phone number.

Patron with specific phone lookup example report

SQL query

select  rol_abonnement.sel_code 'Selection code', count(fin.aid) 'Customer Count', 
sum(checkouts) 'Total checkouts', sum(renewals) 'Total renewals', 
sum(fin.current_checkout) 'Total current checkouts', sum(fin.total) 'Total fine amount owed'
 from 
(select rol.actor_id as aid,sum(CASE WHEN fin_posten.adm_status ='' THEN adm_bedrag END) total , curr_checkout 'current_checkout'
from bicat.rol 
JOIN bicat.fin_posten ON rol.actor_id = fin_posten.actor_id JOIN bicat.rol_abonnement on rol.ext_id = rol_abonnement.ra_id
 JOIN (select actor_id as exemplaren_actor_id, count(CASE WHEN status='U' THEN status END) 'curr_checkout' from bicat.exemplaren group by actor_id) ex ON rol.actor_id = ex.exemplaren_actor_id
where rol_abonnement.eind_datum BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL -4 YEAR))
group by aid) fin
JOIN (SELECT count(CASE WHEN aktie =1 THEN aktie END) 'checkouts', count(CASE WHEN aktie =3 THEN aktie END) 'renewals', actor_id from bicat.loghfdnu where actor_id in (
     select distinct rol.actor_id from bicat.rol JOIN bicat.rol_abonnement on rol.ext_id = rol_abonnement.ra_id where  rol_abonnement.eind_datum BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL -4 YEAR))) group by actor_id) log 
     ON fin.aid = log.actor_id
     JOIN bicat.rol ON fin.aid = rol.actor_id
     JOIN bicat.rol_abonnement ON fin.aid = rol_abonnement.actor_id AND rol.ext_id = rol_abonnement.ra_id
     group by rol_abonnement.sel_code
     limit 9999

Active Customers example report

SQL query

select ab_nr 'Membership Code', def.omschrijving 'Membership Type', count(fin.aid) 'Customer Count',
sum(checkouts) 'Total checkouts', sum(renewals) 'Total renewals',
sum(fin.current_checkout) 'Total current checkouts', sum(fin.total) 'Total fine amount owed'
 from
(select rol.actor_id as aid,sum(CASE WHEN fin_posten.adm_status ='' THEN adm_bedrag END) total , curr_checkout 'current_checkout'
from bicat.rol
JOIN bicat.fin_posten ON rol.actor_id = fin_posten.actor_id JOIN bicat.rol_abonnement on rol.ext_id = rol_abonnement.ra_id
 JOIN (select actor_id as exemplaren_actor_id, count(CASE WHEN status='U' THEN status END) 'curr_checkout' from bicat.exemplaren group by actor_id) ex ON rol.actor_id = ex.exemplaren_actor_id
where rol_abonnement.eind_datum BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL -4 YEAR))
group by aid) fin
JOIN (SELECT count(CASE WHEN aktie =1 THEN aktie END) 'checkouts', count(CASE WHEN aktie =3 THEN aktie END) 'renewals', actor_id from bicat.loghfdnu where actor_id in (
     select distinct rol.actor_id from bicat.rol JOIN bicat.rol_abonnement on rol.ext_id = rol_abonnement.ra_id where  rol_abonnement.eind_datum BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND LAST_DAY(DATE_SUB(CURRENT_DATE, INTERVAL -4 YEAR))) group by actor_id) log
 ON fin.aid = log.actor_id
 JOIN bicat.rol ON fin.aid = rol.actor_id
 JOIN bicat.rol_abonnement ON fin.aid = rol_abonnement.actor_id AND rol.ext_id = rol_abonnement.ra_id
JOIN (
    SELECT DISTINCT ab_nr, instantie_id, omschrijving
    FROM abonnement_def) def
USING (ab_nr, instantie_id)
 group by def.omschrijving
     limit 9999;

Active Customers based on Membership Type example report

SQL query

SELECT IFNULL(v.vestnaam, 'Total') AS BRANCH, COUNT(*) AS REGISTERED
FROM rol r
LEFT JOIN vestiging v
ON r.hoofd_vest = v.vestiging
WHERE r.start_datum BETWEEN '<$startdate>'AND '<$enddate>'
GROUP BY v.vestnaam WITH ROLLUP
LIMIT 9999;

Instructions

• Replace <$startdate> with the Start Date of the month or whichever range you choose.
• Replace <$enddate> with the End Date of the month or whichever range you choose.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

New Customers by Branch example report

SQL query

SELECT IFNULL(i.omschrijving, 'Total') AS LO, COUNT(*) AS REGISTERED
FROM rol r
LEFT JOIN instantie i
ON r.bereik = i.instantie_id
WHERE r.start_datum BETWEEN '<$startdate>'AND '<$enddate>'
GROUP BY i.omschrijving WITH ROLLUP
LIMIT 9999;

Instructions

• Replace <$startdate> with the Start Date of the month or whichever range you choose.
• Replace <$enddate> with the End Date of the month or whichever range you choose.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

New Customers by LO example report

SQL query

SELECT IFNULL(i.omschrijving, 'Grand Total') AS LO, IFNULL(v.vestnaam, 'Total') AS BRANCH, COUNT(*) AS REGISTERED
FROM rol r
LEFT JOIN instantie i
ON r.bereik = i.instantie_id
LEFT JOIN vestiging v
ON r.hoofd_vest = v.vestiging
WHERE r.start_datum BETWEEN '<$startdate>'AND '<$enddate>'
GROUP BY i.omschrijving, v.vestnaam WITH ROLLUP
LIMIT 9999;

Instructions

• Replace <$startdate> with the Start Date of the month or whichever range you choose.
• Replace <$enddate> with the End Date of the month or whichever range you choose.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

New Customers by LO and Branch example report

SQL query

SELECT IFNULL(def.omschrijving, 'Total') AS MEMBERSHIP_TYPE, COUNT(*) AS REGISTERED
FROM rol r
LEFT JOIN rol_abonnement ra
ON r.ext_id = ra.ra_id
LEFT JOIN (
    SELECT DISTINCT ab_nr, instantie_id, omschrijving
    FROM abonnement_def) def
USING (ab_nr, instantie_id)
WHERE r.start_datum BETWEEN '<$startdate>'AND '<$enddate>'
GROUP BY def.omschrijving WITH ROLLUP
LIMIT 9999;

Instructions

• Replace <$startdate> with the Start Date of the month or whichever range you choose.
• Replace <$enddate> with the End Date of the month or whichever range you choose.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

New Customers by Membership example report

SQL query

select
rol_abonnement.hoofd_vest 'Selection Branch',
count(fin.aid) 'Customer Count',
sum(checkouts) 'Total checkouts',
sum(renewals) 'Total renewals',
sum(fin.current_checkout) 'Total current checkouts',
sum(fin.total) 'Total fine amount owed'
from
(select rol.actor_id as aid,sum(adm_bedrag) total , count( CASE WHEN exemplaren.status='U' THEN exemplaren.status END) 'current_checkout'
from bicat.rol JOIN bicat.fin_posten ON rol.actor_id = fin_posten.actor_id JOIN bicat.rol_abonnement on rol.ext_id = rol_abonnement.ra_id JOIN bicat.exemplaren ON rol.actor_id = exemplaren.actor_id
where rol.eind_datum BETWEEN DATE('<$startdate>') AND CURRENT_DATE
group by aid) fin
JOIN
(SELECT count(CASE WHEN aktie =1 THEN aktie END) 'checkouts', count(CASE WHEN aktie =3 THEN aktie END) 'renewals', actor_id from bicat.loghfdnu where actor_id in (
select distinct rol.actor_id from bicat.rol JOIN bicat.rol_abonnement on rol.ext_id = rol_abonnement.ra_id
where rol.eind_datum BETWEEN DATE('<$startdate>') AND CURRENT_DATE)
GROUP BY actor_id) log
ON fin.aid = log.actor_id
JOIN bicat.rol ON fin.aid = rol.actor_id
JOIN bicat.rol_abonnement ON fin.aid = rol_abonnement.actor_id AND rol.ext_id = rol_abonnement.ra_id
LEFT JOIN bctabel on rol_abonnement.sel_code = bctabel.kode
GROUP BY rol_abonnement.hoofd_vest with rollup
     limit 9999

Instructions

• Replace <$startdate> with the Start Date for the report.
• (Optional) Replace CURRENT_DATE with the desired End Date for the report.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).

Home Library example report

SQL query

select CASE WHEN aktie =1 THEN 'Check out' WHEN aktie =2 THEN 'Check in' WHEN aktie =3 THEN 'Renewal' END AS 'Circulation action', aktie_datum AS 'Transaction Date', 
aktie_tijd AS 'Transaction Time', auth_data AS 'Patron Barcode' ,zebra AS 'Item Barcode' from bicat.loghfdnu log
JOIN bicat.exemplaren exem ON log.exem_id = exem.exem_id AND log.instantie_id = exem.instantie_id AND log.actor_id = exem.actor_id
JOIN bicat.actor_auth_middel auth_m ON log.actor_id = auth_m.actor_id AND auth_type ='PAS' AND log.instantie_id = auth_m.instantie_id
where aktie IN (1, 2, 3) 
order by log.instantie_id
limit 9999

Circulation Transactions by Barcode example report

SQL query

SELECT ab_nr AS 'Membership Code', def.omschrijving AS 'Membership Type', COUNT(*) AS Count
FROM rol r
LEFT JOIN rol_abonnement ra
ON r.ext_id = ra.ra_id
LEFT JOIN (
    SELECT DISTINCT ab_nr, instantie_id, omschrijving
    FROM abonnement_def) def
USING (ab_nr, instantie_id)
WHERE ra.start_datum <= CURRENT_DATE AND ra.eind_datum >= CURRENT_DATE
GROUP BY omschrijving
LIMIT 9999;

Unexpired Customers by Membership Type example report

SQL query

select e.eigenaar as Branch, e.plaatsing as 'Shelf Code', sum(e.prijs) as 'Sum of Price', count(*) as 'Item Count' from exemplaren e where e.status in ('B', 'D', 'R', 'T') group by eigenaar, plaatsing
limit 9999

Items on Shelf example report

SQL query

select e.eigenaar 'Branch location', sum(e.prijs) 'Amount', count(*) 'Item Count'  from exemplaren e where e.status='U'  group by eigenaar
limit 9999

Items Checked Out example report

SQL query

select omschr AS "Branch location", count(distinct ex.exem_id) AS "Count of items"  from 
bicat.exemplaren ex 
JOIN bicat.vestiging vs ON ex.eigenaar = vs.vestiging
 where substat='X' group by omschr 
limit 9999

Items Withdrawn example report

SQL query

select temp.exem_id as 'Item ID',
temp.zebra as 'Item Barcode',
temp.eigenaar as 'Placement Branch',
temp.oorsprong as 'Owning Branch',
temp.materiaal as 'RMT Material Type',
temp.status as 'Status',
temp.titelnr as 'Title ID',
temp.updated as 'Updated',
t.momkeys,
t.titstat
from
(select e.exem_id, e.zebra, e.eigenaar, e.oorsprong, e.materiaal, e.status, e.titelnr, e.updated
from exemplaren e
group by titelnr
having titelnr not in
(select titelnr from
exemplaren
where status in ('B','R', 'C', 'D', 'F', 'I', 'K','M', 'O', 'T', 'U', 'Z')
group by titelnr)
and status ='A')
AS temp left join titels t using (titelnr) where temp.status ='A' and temp.updated > '<updateddate>'
limit 9999;

Instructions

• Replace <updateddate> with the start date of the period you would like to display.

Last Item Discarded example report

SQL query

select titelnr as 'title number', exm_titel as 'title', sum(aantalv) as 'No of times loaned' from exemplaren where plaatsing in ('TFI','TFJ') group by titelnr 
limit 9999

Instructions

• To change the shelf location, replace the shelf codes ('TFI','TFJ') with your own shelf codes.
• To pull a single shelf, use ='TFI'.

Title Checkouts by Shelving Location example report

SQL query

SELECT DISTINCT m.subfields AS isbn, m.titelnrs AS 'title numbers', m.count
FROM (
    SELECT recordnr, CASE WHEN SUBSTRING(subfields,3, 1) REGEXP '^[0-8]+' THEN SUBSTRING(subfields,3, 10)
                    WHEN SUBSTRING(subfields,3, 1) = 9 THEN SUBSTRING(subfields,3, 13)
                    END AS subfields
    FROM MARCTITLES
    WHERE tag = '020'
        AND SUBSTRING(subfields,3, 1) REGEXP '^[0-9]+'
    ) i
INNER JOIN (
    SELECT DISTINCT GROUP_CONCAT(recordnr) AS titelnrs, recordnr,
        CASE WHEN SUBSTRING(subfields,3, 1) REGEXP '^[0-8]+' THEN SUBSTRING(subfields,3, 10)
        WHEN SUBSTRING(subfields,3, 1) = 9 THEN SUBSTRING(subfields,3, 13)
        END AS subfields, COUNT(subfields) AS count
    FROM MARCTITLES
    INNER JOIN (
        SELECT titelnr
        FROM titels
        WHERE titstat != 'Z') t
    ON t.titelnr = recordnr
    WHERE tag = '020'
        AND SUBSTRING(subfields,3, 1) REGEXP '^[0-9]+'
    GROUP BY subfields
    HAVING COUNT(subfields) > 1
    ) m
ON i.subfields = m.subfields AND i.recordnr != m.recordnr
GROUP BY m.titelnrs
HAVING LENGTH(isbn) >= 10 AND isbn REGEXP '^[a-zA-Z0-9]*$'
ORDER BY m.count DESC

ISBNs Shared by Multiple Titles example report

SQL query

SELECT *
FROM (
              SELECT l.oorsprong, l.titelnr, t.titel, t.deeltitel, t.auteur, COUNT(*) AS circs,
              (SELECT COUNT(*) FROM loghfdnu WHERE titelnr = l.titelnr GROUP BY titelnr) AS life_time_circ,
    ROW_NUMBER() OVER (PARTITION BY oorsprong ORDER BY COUNT(*) DESC, (SELECT COUNT(*)
                             FROM loghfdnu WHERE titelnr = l.titelnr GROUP BY titelnr) DESC) AS ranking
              FROM loghfdnu l
              LEFT JOIN titels t
              USING (titelnr)
              WHERE aktie IN (1, 3)
                             AND l.actor_id NOT LIKE '-%'
                             AND l.titelnr != 0
                             AND l.aktie_datum BETWEEN '<$startdate>'AND '<$enddate>'
                             AND l.materiaal = '<$rmt>'
              GROUP BY l.oorsprong, l.titelnr, t.titel, t.deeltitel, t.auteur) ranked_data
WHERE ranking <= 10
LIMIT 9999;

Instructions

• Replace <$startdate> with the Start Date of the month.
• Replace <$enddate> with the End Date of the month.
• Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01).
• Replace <$rmt> with the RMT code. If a group of material types are to be evaluated together, truncation such as 'B%' is recommended.
• For material types that might circulate less, such as audiobooks, the 10 in the last WHERE clause in the query can be changed to be 15 or 20, making the output the top 15 or top 20.

Top 10 Circulating Titles By Branch example report

SQL query

SELECT t.titelnr AS `Titel ID`, t.titel AS Title, t.deeltitel AS Subtitle, t.auteur AS Author, COUNT(l.loghfd_id) AS `Total Circs`
FROM loghfdnu l
LEFT JOIN titels t
USING (titelnr)
WHERE aktie IN (<$circtype>)
AND l.actor_id NOT LIKE '-%'
              AND l.titelnr != 0
              AND (t.materiaal = ' <$rmt> ' OR t.materiaalj = ' <$rmt> ')
              AND YEAR(l.leen_dat) = <$year>
GROUP BY t.titelnr
ORDER BY `Total Circs` DESC
LIMIT 10;

Instructions

• <$circtype> can only be either 1 (checkouts) or 1,3 (checkouts and renewals).

Top 10 Circulating Titles for 2022 example report

SQL query

SELECT f.factuurnr AS invoice_id, f.leveranc AS vendor, f.factuurdatum AS invoice_date,
       CASE WHEN SUM(p.amount) IS NULL THEN 0 ELSE SUM(p.amount) END AS paid,
    CASE WHEN SUM(o.amount) IS NULL THEN 0 ELSE SUM(o.amount) END AS outstanding,
    CASE WHEN SUM(o.expired_amount) IS NULL THEN 0 ELSE SUM(o.expired_amount) END AS expired_outstanding
FROM order_factuur f
LEFT JOIN (
       SELECT f.ordfact_id, f.factuurnr AS invoice_id, r.leveranc AS vendor, r.orderdatum AS order_date,
             f.factuurdatum AS invoice_date, r.betaaldatum AS paid_date, ROUND(r.prijs/100, 2) AS amount
       FROM order_regels r
       LEFT JOIN order_factuur f
       USING (ordfact_id)
       WHERE r.betaalstatus = 'B') p
ON p.ordfact_id = f.ordfact_id
LEFT JOIN (
       SELECT f.ordfact_id, f.factuurnr AS invoice_id, r.leveranc AS vendor, r.orderdatum AS order_date,
             f.factuurdatum AS invoice_date, r.betaaldatum AS paid_date,
        CASE WHEN ex_status != 'V' THEN ROUND(r.prijs/100, 2) END AS amount,
        CASE WHEN ex_status = 'V' THEN ROUND(r.prijs/100, 2) END AS expired_amount
       FROM order_regels r
       LEFT JOIN order_factuur f
       USING (ordfact_id)
       WHERE betaalstatus = 'O') o
ON o.ordfact_id = f.ordfact_id
WHERE f.factuurdatum BETWEEN '<$start date>' AND '<$enddate>'
       AND (p.amount IS NOT NULL OR o.amount IS NOT NULL OR o.expired_amount IS NOT NULL)
GROUP BY f.factuurnr
ORDER BY invoice_date
LIMIT 9999;

Instructions

•    Replace <$startdate> with the Start Date for the report
•    Replace <$enddate> with the End Date for the report
•    Dates must be entered in the following support format: yyyy-mm-dd (e.g., 2022-01-01)

Invoice Summary example report

SQL query

select
wr.wise_rol as wise_role,
wrl.omschr as role,
wr.actor_id,
func_bx_naam_actor_utf8(wr.actor_id,'A') as staffName,
wr.vestiging as branch,
v.instantie_id as instance_id
from wise_rol wr,wise_rollen wrl,actor a, vestiging v
where wr.wise_rol <> ''
and wr.wise_rol = wrl.rol
and a.actor_id = wr.actor_id
and wr.vestiging = v.vestiging
order by v.instantie_id, wr.actor_id asc
limit 9999

Staff User Privileges example report

SQL query

select
wr.wise_rol,
wrl.omschr as role,
wr.actor_id,
func_bx_naam_actor_utf8(wr.actor_id,'A') as staffName,
wr.vestiging as branch,
v.instantie_id as instance_id
from wise_rol wr,wise_rollen wrl,actor a, vestiging v
where wr.wise_rol <> ''
and wr.wise_rol = wrl.rol
and a.actor_id = wr.actor_id
and wr.vestiging = v.vestiging
and v.instantie_id = '<$libraryid>'
order by v.instantie_id, wr.actor_id asc
limit 9999

Instructions

• Replace <$libraryid> with the library ID for the report (e.g., I000, I060, etc.).

Staff User Privileges for a specific library example report