SQL Reports Patrons

From Koha Test Wiki MediaWiki Postgres

Patron Reports

SQL Reports Main Library | SQL Reports Holds | SQL Reports Circulation

Patron Statistics

General

Export patrons

Developer: Heinrich Hartl
Module: Borrowers
Purpose: To create a sample spreadsheet for patron import or to export patron data from one library for import into another library
Status: Completed

SELECT cardnumber, surname, 
          firstname, title, othernames, initials, streetnumber, streettype, address, address2, city, state, zipcode, country, email, phone, mobile, fax,  
          emailpro, phonepro, B_streetnumber, B_streettype, B_address, B_address2, B_city, B_state, B_zipcode, B_country, B_email, B_phone, dateofbirth, 
          branchcode, categorycode, dateenrolled, dateexpiry, gonenoaddress, lost, debarred, debarredcomment, contactname, contactfirstname, contacttitle,
          guarantorid, borrowernotes, relationship, sex, password, flags, userid, opacnote, contactnote, sort1, sort2, altcontactfirstname,
          altcontactsurname, altcontactaddress1, altcontactaddress2, altcontactaddress3, altcontactstate, altcontactzipcode, altcontactcountry, altcontactphone,
          smsalertnumber, privacy
 
FROM borrowers 
WHERE branchcode=<<Select your branch|branches>> 
ORDER BY surname ASC, firstname ASC

Patrons and Issues

Patrons with Checked Out Items

Developer: Nora Blake
Module: Circulation
Purpose: List of items checked out to patrons
Status: Complete

  SELECT issues, biblio.title, author, surname, firstname, borrowers.sort1, 
         items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate 
  FROM issues 
  LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber 
  LEFT JOIN items ON issues.itemnumber=items.itemnumber 
  LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
  WHERE issues.branchcode=<<Checked out at|branches>>
  ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title

Patrons with No Checkouts

Developer: Nicole C. Baratta, ByWater Solutions, revised Nick Clemens, VOKAL Library System
Module: Patrons
Purpose: Patrons who haven't checked out in a specific timeframe
Status: Completed

SELECT surname,firstname,cardnumber
FROM borrowers b
LEFT OUTER JOIN (SELECT DISTINCT borrowernumber  FROM statistics WHERE datetime BETWEEN <<Date1|date>> AND <<Date 2|date>>  AND type="issue") foo ON b.borrowernumber=foo.borrowernumber
WHERE foo.borrowernumber IS NULL

Anonymous Patron Account Report

Developer: Barton Chittenden, ByWater Solutions
Purpose: A list of items checked out to the Anonymous Patron account
Status: Completed

SELECT
    itemnumber,
    title, 
    issuedate, 
    returndate
FROM
    old_issues 
    INNER JOIN systempreferences 
        ON ( 
                old_issues.borrowernumber = systempreferences.value 
                AND systempreferences.variable = 'anonymouspatron' 
           )
    LEFT JOIN items USING (itemnumber)
    LEFT JOIN biblio USING (biblionumber)

Patrons and Age

Patron Birthday Report

Developer: Nicole C. Baratta, ByWater Solutions
Module: patrons
Purpose: Patrons who are under the age of 17 that have a birthday this month
Status: Complete

SELECT firstname, surname, address, address2, city, 
       zipcode, dateofbirth 
FROM borrowers 
WHERE MONTH(dateofbirth) = <<Month (mm)>> 
      AND DATEDIFF(<<Last date of month (yyyy-mm-dd)|date>>, dateofbirth) < ((17*365)+4)

Patrons of specific age range

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: patrons
Purpose: This report shows patrons between the age of 12 and 13
Status: Complete

SELECT cardnumber, firstname, surname, dateofbirth, 
      timestampdiff(year,dateofbirth,curdate()) AS age 
      categorycode 
FROM borrowers 
WHERE timestampdiff(year,dateofbirth,curdate()) IN (12,13)

Patrons Whose Age Violates Category Age Limits

Developer: Kyle M Hall, ByWater Solutions
Module: circ
Purpose: Lists all patrons whose age is above or below the maximum and/or minimum age range for his or her category
Status: Complete

SELECT
   c.description AS category,
   b.surname,
   b.firstname,
   b.dateofbirth AS DoB,
   c.dateofbirthrequired AS MinAge,
   c.upperagelimit AS MaxAge,
   FLOOR(DATEDIFF (NOW(), b.dateofbirth)/365)  AS age  
FROM
   borrowers b 
LEFT JOIN
   categories c USING ( categorycode ) 
WHERE
   (
      c.upperagelimit > 0 
      OR c.dateofbirthrequired > 0 
   ) 
HAVING
   age < MinAge OR ( age > MaxAge AND MaxAge > 0 )

Count of discharges, select date range

Developer: Md. Mubassir Ahsan
Module: Patrons
Purpose: Count the discharges monthly, yearly or any defined date range.
Status: Complete

SELECT COUNT(discharges.borrower)  AS "Total Discharges"
FROM discharges
WHERE 
validated > <<Beginning of first range (dd-mm-yyyy)|date>>
AND validated < <<End of first range (dd-mm-yyyy)|date>>

Count of Active Patrons by Age Group in a Specified Date Range

Developer: Spencer Smith
Module: Patrons
Purpose: Returns a count of patrons grouped by age range, with activity in a certain date range restricted by their home branch.
Status: Complete

SELECT
    (CASE
        WHEN timestampdiff(year,dateofbirth,curdate()) < 5 THEN '0 to 4'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 5 AND 12 THEN '5 to 12'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 13 AND 18 THEN '13 to 18'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 19 AND 25 THEN '19 to 25'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 26 AND 32 THEN '26 to 32'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 33 AND 40 THEN '33 to 40'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 41 AND 50 THEN '41 to 50'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 51 AND 60 THEN '51 to 60'
        WHEN timestampdiff(year,dateofbirth,curdate()) BETWEEN 61 AND 70 THEN '61 to 70'
        WHEN timestampdiff(year,dateofbirth,curdate()) >= 70 THEN '70 +'
        WHEN timestampdiff(year,dateofbirth,curdate()) IS NULL THEN 'Not Filled In (NULL)'
    END) AS age_range,
    COUNT(borrowernumber) AS Patrons
 
    FROM
    borrowers
WHERE
    borrowernumber IN (
        SELECT
            borrowernumber
        FROM
            statistics
        WHERE
             DATE( datetime ) BETWEEN <<Has activity BETWEEN (YYYY-MM-dd)|date>> AND <<and|date>>
    ) AND borrowers.branchcode=<<branchcode|branches>>
 
    GROUP BY age_range
 
    ORDER BY age_range

New Patrons

New Patron List (previous month)

Developer: Jane Wagner, PTFS
Module: circ
Purpose:
Status: Complete

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, borrowers.dateenrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
ORDER BY borrowers.surname ASC

New Patrons by Category at Branch

Developer: Sharon Moreland
Module: Circulation
Purpose: New patrons added
Status: Complete

  SELECT branchcode,categorycode,COUNT(*)
  FROM borrowers WHERE MONTH(dateenrolled) = <<Month enrolled (mm)>> AND YEAR(dateenrolled)= <<Year enrolled (yyyy)>>
  GROUP BY branchcode,categorycode 
  ORDER BY branchcode

New Patrons by Category in Date Range

Developer: Nicole C. Baratta, ByWater Solutions
Module: Circulation
Purpose: New patrons added by category in a date range (even if they have been deleted since)
Status: Complete

SELECT categorycode, COUNT(borrowernumber) AS 'new patrons'
FROM (SELECT borrowernumber, categorycode, dateenrolled FROM borrowers 
UNION ALL
SELECT borrowernumber, categorycode, dateenrolled FROM deletedborrowers) AS patrons 
WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>  
GROUP BY categorycode

New Patron Count (previous month)

Developer: Jane Wagner, PTFS
Module: Patron
Purpose:
Status: Complete

SELECT COUNT(*) AS 'New Patrons Last Month' 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month)

New Patron Count (by Branch/Category) (previous month)

Developer: Jane Wagner, PTFS
Module: Patron
Purpose: Count of new patrons enrolled in the previous month, by branch and category code
Status: Complete

SELECT branchcode, categorycode, COUNT(branchcode) AS NumberEnrolled 
FROM borrowers 
WHERE borrowers.dateenrolled >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') AND borrowers.dateenrolled <= LAST_DAY(now() - interval 1 month) 
GROUP BY branchcode, categorycode

New Patrons by Branch (year to date)

Developer: Jane Wagner, PTFS
Module: Patron
Purpose:
Status: Complete

SELECT branchcode, categorycode, COUNT(branchcode) AS NumberEnrolled 
FROM borrowers 
WHERE YEAR(borrowers.dateenrolled) = YEAR(NOW()) 
GROUP BY branchcode, categorycode

Count of New Patrons

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: Count of patrons added between two dates at a branch
Status: Complete

SELECT COUNT(borrowernumber) AS 'New Patrons' 
FROM borrowers 
WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> 
      AND <<and (yyyy-mm-dd)|date>> AND branchcode=<<Branch|branches>>

Patrons and Branches

Patrons, Categories, and Permissions

Patrons with Staff Permissions

Developer: Ian Walls, ByWater Solutions
Module: Patrons
Purpose: List of patrons/staff with their permission levels
Status: Complete

SELECT borrowernumber, firstname, surname, categorycode, 
       description, flags 
FROM borrowers 
JOIN user_permissions USING (borrowernumber) 
JOIN permissions USING (code) 
UNION (
      SELECT borrowernumber, firstname, surname, 
             categorycode, 'module-level permissions; 
             1 is superlibrarian' AS description, flags 
      FROM borrowers 
      WHERE flags > 0) 
ORDER BY borrowernumber ASC

Superlibrarians

Developer: Nicole C. Baratta, Joy Nelson and Elliott Davis, ByWater Solutions
Module: Patrons
Purpose: List of patrons/staff with superlibrarian permission
Status: Complete

SELECT borrowernumber, firstname, surname, categorycode, flags 
FROM borrowers 
WHERE flags%2=1 
ORDER BY borrowernumber ASC

Patrons with staff permission, and if they are superlibrarians

Developer: Tomás Cohen Arazi, Universidad Nacional de Córdoba
Module: Patrons
Purpose: Patrons with staff permission, and if the are superlibrarians
Status: Complete

SELECT
  surname AS 'Surname',
  firstname AS 'Firstname',
  Cardnumber,
  categories.description AS 'Patron type',
  Superlibrarian
FROM (
  SELECT
    surname, firstname,
    CONCAT('<a href="http://',(SELECT value FROM systempreferences WHERE variable='staffClientBaseURL'),
    '/cgi-bin/koha/members/moremember.pl?borrowernumber=',bn.borrowernumber,'">',cardnumber,'</a>') AS 'Cardnumber',
    CASE WHEN flags%2=1 THEN 'yes' WHEN flags%2=0 THEN 'no' END AS Superlibrarian, categorycode
  FROM (
    SELECT borrowernumber
    FROM borrowers
    WHERE flags > 0
    UNION
    SELECT DISTINCT borrowernumber
    FROM user_permissions) bn 
    LEFT JOIN borrowers ON (borrowers.borrowernumber=bn.borrowernumber)
) a
LEFT JOIN categories
ON (a.categorycode=categories.categorycode)
ORDER BY surname ASC

Patron Permissions

Developer: Christopher Brannon & Nick Clemens
Module: Patrons
Purpose: Lists all patrons with any permissions set and details their permissions.
Status: Complete

SELECT surname,firstname,cardnumber, categorycode, branchcode, 
IF(flags MOD 2,'Set','') AS SuperLib,
IF(MOD(flags DIV 2,2),'All parameters',GROUP_CONCAT(IF(u_p.module_bit=1,p.code,'') SEPARATOR ' ' ) ) AS "CircPermissions", 
IF(MOD(flags DIV 4,2),'Set','') AS 'View staff interface',
IF(MOD(flags DIV 8,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=3,p.code,'') SEPARATOR ' ' )  ) AS ManParams,
IF(MOD(flags DIV 16,2),'Set','') AS 'Add/modify patrons',
IF(MOD(flags DIV 32,2),'Set','') AS 'Modify permissions',
IF(MOD(flags DIV 64,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=6,p.code,'') SEPARATOR ' ' )  ) AS ReservePermissions,
IF(MOD(flags DIV 128,2),'Set','') AS BorrowBooks,
IF(MOD(flags DIV 512,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=9,p.code,'') SEPARATOR ' ' )  ) AS EditCatalogue,
IF(MOD(flags DIV 1024,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=10,p.code,'') SEPARATOR ' ' )  ) AS UpdateCharges,
IF(MOD(flags DIV 2048,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=11,p.code,'') SEPARATOR ' ' )  ) AS Acquisition,
IF(MOD(flags DIV 4096,2),'Set','') AS Management,
IF(MOD(flags DIV 8192,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=13,p.code,'') SEPARATOR ' ' )  ) AS Tools,
IF(MOD(flags DIV 16384,2),'Set','') AS EditAuthories,
IF(MOD(flags DIV 32768,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=15,p.code,'') SEPARATOR ' ' )  ) AS Series,
IF(MOD(flags DIV 65536,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=16,p.code,'') SEPARATOR ' ' )  ) AS Reports,
IF(MOD(flags DIV 131072,2),'Set','') AS StaffAccess,
IF(MOD(flags DIV 262144,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=18,p.code,'') SEPARATOR ' ' )  ) AS CourseReserves,
IF(MOD(flags DIV 524288,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=19,p.code,'') SEPARATOR ' ' )  ) AS Plugins
FROM borrowers b
LEFT JOIN user_permissions  u_p ON b.borrowernumber=u_p.borrowernumber
LEFT JOIN permissions p ON u_p.code=p.code
WHERE flags>0  OR u_p.module_bit>0
GROUP BY b.borrowernumber
ORDER BY categorycode,branchcode,surname,firstname ASC

Permissions Check

Developer: Christopher Brannon
Module: Patrons
Purpose: Search for ANY patrons that have some dangerous flags set. Modify the WHERE statement to watch for flags you want to keep an eye on.
Status: Complete

SELECT surname AS 'Last Name',firstname AS 'First Name',cardnumber AS 'Card Number',categorycode,userid AS 'UserID',branchcode AS 'Library',Super AS 'superlibrarian',A AS 'circulate',SubA AS 'circulate subs',B AS 'catalogue',C AS 'parameters',SubC AS 'parameters subs',D AS 'borrowers',E AS 'permissions',F AS 'reserveforothers',SubF AS 'reserveforothers subs',G AS 'borrow',I AS 'editcatalogue',SubI AS 'editcatalogue subs',J AS 'updatecharges',K AS 'acquisition',SubK AS 'acquisition subs',L AS 'management',M AS 'tools',SubM1 AS 'tools subs (Pt 1)',SubM2 AS 'tools subs (Pt 2)',N AS 'editauthorities',O AS 'serials',SubO AS 'serials subs',P AS 'reports',SubP AS 'reports subs',Q AS 'staffaccess'
FROM (SELECT b.surname,b.firstname,b.cardnumber,b.userid,b.branchcode,b.categorycode,@CHECK:=b.flags AS 'CheckQ',IF(@Check-131072>=0,@Q:="On",@Q:="Off") AS 'Q',
IF(@Check-131072>=0,@CHECK:=@Check-131072,@CHECK) AS 'CheckP',IF(@Check-65536>=0,@P:="On",@P:="Off") AS 'P',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='16' AND borrowernumber=b.borrowernumber) AS SubP,
IF(@Check-65536>=0,@CHECK:=@Check-65536,@CHECK) AS 'CheckO',IF(@Check-32768>=0,@O:="On",@O:="Off") AS 'O',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='15' AND borrowernumber=b.borrowernumber) AS SubO,
IF(@Check-32768>=0,@CHECK:=@Check-32768,@CHECK) AS 'CheckN',IF(@Check-16384>=0,@N:="On",@N:="Off") AS 'N',
IF(@Check-16384>=0,@CHECK:=@Check-16384,@CHECK) AS 'CheckM',IF(@Check-8192>=0,@M:="On",@M:="Off") AS 'M',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN "a%" AND "i%") AS SubM1,(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber AND up.code BETWEEN "j%" AND "z%") AS SubM2,
IF(@Check-8192>=0,@CHECK:=@Check-8192,@CHECK) AS 'CheckL',IF(@Check-4096>=0,@L:="On",@L:="Off") AS 'L',
IF(@Check-4096>=0,@CHECK:=@Check-4096,@CHECK) AS 'CheckK',IF(@Check-2048>=0,@K:="On",@K:="Off") AS 'K',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='11' AND borrowernumber=b.borrowernumber) AS SubK,
IF(@Check-2048>=0,@CHECK:=@Check-2048,@CHECK) AS 'CheckJ',IF(@Check-1024>=0,@J:="On",@J:="Off") AS 'J',
IF(@Check-1024>=0,@CHECK:=@Check-1024,@CHECK) AS 'CheckI',IF(@Check-512>=0,@I:="On",@I:="Off") AS 'I',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='9' AND borrowernumber=b.borrowernumber) AS SubI,
IF(@Check-512>=0,@CHECK:=@Check-512,@CHECK) AS 'CheckH',IF(@Check-256>=0,@H:="On",@H:="Off") AS 'H',
IF(@Check-256>=0,@CHECK:=@Check-256,@CHECK) AS 'CheckG',IF(@Check-128>=0,@G:="On",@G:="Off") AS 'G',
IF(@Check-128>=0,@CHECK:=@Check-128,@CHECK) AS 'CheckF',IF(@Check-64>=0,@F:="On",@F:="Off") AS 'F',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='6' AND borrowernumber=b.borrowernumber) AS SubF,
IF(@Check-64>=0,@CHECK:=@Check-64,@CHECK) AS 'CheckE',IF(@Check-32>=0,@E:="On",@E:="Off") AS 'E',
IF(@Check-32>=0,@CHECK:=@Check-32,@CHECK) AS 'CheckD',IF(@Check-16>=0,@D:="On",@D:="Off") AS 'D',
IF(@Check-16>=0,@CHECK:=@Check-16,@CHECK) AS 'CheckC',IF(@Check-8>=0,@C:="On",@C:="Off") AS 'C',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='3' AND borrowernumber=b.borrowernumber) AS SubC,
IF(@Check-8>=0,@CHECK:=@Check-8,@CHECK) AS 'CheckB',IF(@Check-4>=0,@B:="On",@B:="Off") AS 'B',
IF(@Check-4>=0,@CHECK:=@Check-4,@CHECK) AS 'CheckA',IF(@Check-2>=0,@A:="On",@A:="Off") AS 'A',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='1' AND borrowernumber=b.borrowernumber) AS SubA,
IF(@Check-2>=0,@CHECK:=@Check-2,@CHECK) AS 'CheckSuper',IF(@Check>0,"On","Off") AS "Super"
FROM borrowers b
LEFT JOIN categories USING (categorycode)) AS MainFlags
WHERE MainFlags.Super="On" OR MainFlags.E="On" OR MainFlags.M="On" OR MainFlags.SubM1 LIKE "%delete_anonymize_patrons%" OR MainFlags.SubM1 LIKE "%edit_calendar%" OR MainFlags.SubM1 LIKE "%edit_news%" OR MainFlags.SubM1 LIKE "%edit_notice_status_triggers%" OR MainFlags.SubM1 LIKE "%edit_notices%" OR MainFlags.SubM1 LIKE "%edit_news%" OR MainFlags.SubM1 LIKE "%items_batchdel%" OR MainFlags.SubM1 LIKE "%schedule_tasks%" OR MainFlags.SubM1 LIKE "%view_system_logs%" OR MainFlags.SubP LIKE "%create_reports%"
ORDER BY surname, firstname ASC

Active Patrons

List Patrons with the SEEN attribute

Developer: Brenda Turnbull, LiveWire CIC
Module: Statistical (Patrons)
Purpose: List the patrons who have used eBooks or public computers (recorded by the SEEN attribute) for a specified date.
Status: Complete

SELECT a.borrowernumber, a.code, a.attribute AS DateSeen, p.surname, p.firstname, p.cardnumber
 FROM borrower_attributes a
LEFT JOIN borrowers p USING (borrowernumber) 
WHERE a.code = 'SEEN'  AND  STR_TO_DATE(a.attribute,'%d-%b-%Y') =<< date|date>>

List Active Patrons

Developer: Nicole C. Baratta, ByWater Solutions
Module: Statistical (Circulation, Reports)
Purpose: List Active Patrons since a specific date
Status: Complete

SELECT DISTINCT surname, firstname, cardnumber, email, address, 
                address2, city, state, zipcode 
FROM borrowers
WHERE borrowernumber IN 
      (SELECT borrowernumber 
       FROM statistics 
       WHERE borrowernumber = borrowernumber 
             AND datetime >= <<Has activity since (YYYY-MM-dd)|date>>) 
ORDER BY surname, firstname

Count Active Patrons

Developer: Mike Hafen
Module: Patrons
Purpose: A report for finding patrons who are checking out materials
Status: Completed

SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber)
FROM (
  SELECT issuedate, borrowernumber FROM old_issues
 UNION ALL
  SELECT issuedate, borrowernumber FROM issues
) AS all_issues
LEFT JOIN borrowers USING (borrowernumber)
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode

Count of active patrons by category, filtered by date range

Developer: Barton Chittenden, ByWater Solutions
Module: Statistical (Circulation, Reports)
Purpose: Break down count of active borrowers by category
Status: Complete

SELECT
    count(*) AS count,
    categorycode
FROM
    borrowers
WHERE
    borrowernumber IN (
        SELECT
            borrowernumber
        FROM
            statistics
        WHERE
             DATE( datetime ) BETWEEN <<Has activity BETWEEN (YYYY-MM-dd)|date>> AND <<and|date>>
    )
GROUP BY
    categorycode

Count Active Patrons by Category for a Specific Month

Developer: Jesse Weaver
Module: Statistical (Circulation, Reports)
Purpose: Count Active Patrons by Category for a Specific Month
Status: Complete

SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) 
FROM old_issues
  LEFT JOIN borrowers USING (borrowernumber) 
GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode

List Active Patrons with over $20 in fines

Developer: Nick Clemens, ByWater Solutions
Module: Statistical (Circulation, Reports)
Purpose: List Patrons active in 6 months with fines over $20
Status: Complete

SELECT
COUNT(DISTINCT borrowernumber)
FROM borrowers
LEFT JOIN statistics USING (borrowernumber)
LEFT JOIN
(SELECT borrowernumber, 1 AS highfines
FROM accountlines
GROUP BY borrowernumber HAVING SUM(amountoutstanding) > 20)
finesum USING (borrowernumber)
WHERE
type IN ('issue','renew') AND
DATE(datetime) > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND
highfines=1

Count of Renewed Memberships in Date Range

Developer: Caroline Cyr La Rose, inLibro
Module: Action logs
Purpose: Gives a count of renewed memberships in specified date range
Status: Complete

SELECT COUNT(timestamp) AS 'Renewed Memberships' 
FROM action_logs
WHERE DATE(timestamp) BETWEEN <<Entre (aaaa-mm-jj)|date>> 
      AND <<et (aaaa-mm-jj)|date>>
AND info LIKE 'Membership renewed'

Renewed Memberships in Date Range with Patron Details

Developer: Caroline Cyr La Rose, inLibro
Module: Action logs
Purpose: Gives a list of renewed memberships in specified date range with patron info (cardnumber, first & last name)
Status: Complete

SELECT
  CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',borrowernumber,'\">',cardnumber,'</a>') AS "Cardnumber",
  surname AS "Last name",
  firstname AS "First name"
FROM borrowers
RIGHT JOIN action_logs ON borrowers.borrowernumber=action_logs.object
WHERE action_logs.action LIKE 'renew' AND action_logs.module LIKE 'members' AND date(action_logs.timestamp) BETWEEN <<Between|date>> AND <<and|date>>
ORDER BY surname

Patrons Who Borrowed More Than X Documents in Date Range

Developer: Caroline Cyr La Rose, inLibro
Module: Borrowers, Circulation
Purpose: Gives a list of patrons who borrowed more than a specified number of documents in a specified date range
Status: Complete

SELECT
  b.surname AS 'Last Name',
  b.firstname AS 'First Name',
  CASE b.categorycode --Enter your own patron category codes here
    WHEN 'ADMIN' THEN 'Administration'
    WHEN 'ADULTE' THEN 'Adulte - Résident'
    WHEN 'ADULTE-EXT' THEN 'Adulte Non-Résident'
    WHEN 'CONSULT' THEN 'Consultations sur place'
    WHEN 'EMPMASSIF' THEN 'Emprunts massifs'
    WHEN 'INACTIF' THEN 'Inactif'
    WHEN 'JEUNE-EXT' THEN 'Jeune - Non-Résident'
    WHEN 'JEUNE' THEN 'Jeune - Résident'
    WHEN 'ORGANISME' THEN 'Organismes'
    WHEN 'PROF' THEN 'Professionnel'
    WHEN 'PROVISOIRE' THEN 'Provisoire'
    WHEN 'AVERIFIER' THEN 'À vérifier'
    ELSE 'Error' END AS 'Patron Category',
  b.cardnumber AS "Card number",
  COUNT(i.issue_id) AS 'Loan qty'
FROM
  borrowers b
LEFT JOIN
  (SELECT * FROM issues UNION SELECT * FROM old_issues) i
  USING (borrowernumber)
WHERE
  i.issuedate BETWEEN <<Between|date>> AND <<and|date>>
GROUP BY
  b.borrowernumber
  HAVING COUNT(i.issue_id) > <<Minimum qty of loans>>
ORDER BY
  surname, firstname

Count of Patrons by Category Who Borrowed More Than X Documents in Date Range

Developer: David Bourgault, inLibro
Module: Borrowers, Circulation
Purpose: Gives a count of patrons per patron category who borrowed more than a specified number of documents in a specified date range
Status: Complete

SELECT
    CASE categorycode --Enter your own patron category codes here
        WHEN 'ADMIN' THEN 'Administration'
        WHEN 'ADULTE' THEN 'Adulte - Résident'
        WHEN 'ADULTE-EXT' THEN 'Adulte Non-Résident'
        WHEN 'CONSULT' THEN 'Consultations sur place'
        WHEN 'EMPMASSIF' THEN 'Emprunts massifs'
        WHEN 'INACTIF' THEN 'Inactif'
        WHEN 'JEUNE-EXT' THEN 'Jeune - Non-Résident'
        WHEN 'JEUNE' THEN 'Jeune - Résident'
        WHEN 'ORGANISME' THEN 'Organismes'
        WHEN 'PROF' THEN 'Professionnel'
        WHEN 'PROVISOIRE' THEN 'Provisoire'
        WHEN 'AVERIFIER' THEN 'À vérifier'
        ELSE 'Erreur' 
    END AS 'Category',
    count(*) AS "User Count" FROM
    (
        SELECT b.categorycode, count(i.issue_id) FROM borrowers b
        LEFT JOIN
            (
                SELECT * FROM issues
                UNION
                SELECT * FROM old_issues
            ) i
        USING (borrowernumber)
        WHERE i.issuedate BETWEEN <<Between|date>> AND <<and|date>>
        GROUP BY b.borrowernumber 
        HAVING COUNT(i.issue_id) > <<Minimum qty of loans>>
    ) c
GROUP BY categorycode
ORDER BY categorycode

Patrons renewed within a date range

Developer: Kelly McElligott, ByWater Solutions
Module: Borrowers
Purpose: Lists patrons renewed within a given date range
Status: Complete

SELECT firstname, surname, dateenrolled, date_renewed, cardnumber
FROM borrowers
WHERE date_renewed BETWEEN <<Start date|date>> AND <<End date|date>>

Expired/ Deleted Patrons

Count of Expired Patrons

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: Count of patrons who's cards have expired before today
Status: Complete

SELECT COUNT(cardnumber) AS count
FROM borrowers  
WHERE dateexpiry > <<Today's Date (yyyy-mm-dd)|date>>

Expired Patrons without Checkouts

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: List patrons expired in a specific year who do not currently have any checkouts
Status: Complete

  SELECT surname, firstname, borrowernumber 
  FROM borrowers 
  WHERE borrowernumber 
  NOT IN (SELECT borrowernumber FROM issues) 
  AND YEAR(dateexpiry) = <<Year>>

Expired patrons with Checkouts

Developer: David Roberts, PTFS Europe
Module: Patrons
Purpose: List of expired patrons with loans
Status: Complete

SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber, surname, firstname 
  FROM borrowers 
  WHERE borrowernumber IN (SELECT borrowernumber FROM issues) 
  AND dateexpiry <= NOW()

Expired Patrons and Funds Owed

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: A list of expired patrons with the money they owe and their guarantor information
Status: Complete

SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, 
       p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),'') AS guarantor, 
       p.relationship, ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
FROM borrowers p
LEFT JOIN accountlines a USING (borrowernumber)
LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
WHERE p.dateexpiry < NOW() 
GROUP BY p.borrowernumber
ORDER BY p.dateexpiry ASC

Count of patrons whose cards will expire after a specific date, group by category

Developer: E. Guertin
Module: Patrons
Purpose: Count of patrons with valid card at a specific date (expiration after a specific date), count by patron category.
Status: Complete

SELECT categorycode, COUNT(*) 
FROM borrowers
WHERE dateexpiry > <<YYYY-MM-DD>>
GROUP BY categorycode

Count of deleted patrons

Developer: George H. Williams (Northeast Kansas Library System)
Module: Patrons
Purpose: Count patrons deleted during a specified date range grouped by home branch and category
Status: Complete
Version: Should work in any version provided you have the BorrowersLog system preference set to log changes to borrowers
Notes: If you write the query to say "WHERE action_logs.action = 'DELETE'" Koha will give you an error message saying that "DELETE" is an SQL keyword that you can't use in the Koha reporting module. If you write it, instead, to say "WHERE actionlogs.action LIKE 'DELET%'" you can work around the keyword issue because you're not using the actual keyword.

SELECT
  deletedborrowers.branchcode AS PATRON_HOME_LIBRARY,
  deletedborrowers.categorycode AS PATRON_CATEGORY,
  Count(deletedborrowers.borrowernumber) AS COUNT_OF_PATRONS_DELETED
FROM
  action_logs JOIN
  deletedborrowers
    ON deletedborrowers.borrowernumber = action_logs.object
WHERE
  (action_logs.timestamp BETWEEN <<between the beginning of the day ON "START DATE"|date>>  AND (<<and the end of the day ON "END DATE"|date>>+ INTERVAL 1 DAY)) AND
  action_logs.action LIKE 'DELET%'
GROUP BY
  PATRON_HOME_LIBRARY,
  PATRON_CATEGORY
  WITH ROLLUP

List of patrons not using the OPAC or SIP

Developer: Andrew Fuerste-Henry (ByWater Solutions)
Module: Patrons
Purpose: List patrons who last logged into the OPAC or connected via SIP more than 12 months ago
Status: Complete
Version: 18.11

SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber, 
	cardnumber, 
    concat(surname,', ',firstname) AS name, 
    lastseen
FROM borrowers
WHERE (date(lastseen)<=date_sub(curdate(), interval 12 month) OR lastseen IS NULL)
ORDER BY lastseen

List of patrons expired in date range

Developer: Caroline Cyr La Rose, inLibro
Module: Patrons
Purpose: List of patrons whose card expires in a date range (past or future). (Note: I put AS "" in my reports to rename columns since my clients don't necessarily speak English. I left them here, but you can remove them)
Status: Complete
Version: 19.05

SELECT
    cardnumber AS "Card number",
    surname AS "Surname",
    firstname AS "First name",
    dateexpiry AS "Expiry date",
    CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">View patron file</a>' ) AS ""
FROM
    borrowers
WHERE
    dateexpiry BETWEEN <<Expires bewteen|date>> AND <<and|date>>

List of patrons expired in date range (just cardnumbers for batch modification)

Developer: Caroline Cyr La Rose, inLibro
Module: Patrons
Purpose: List of patrons whose card expires in a date range (past or future).
Status: Complete
Version: 19.05

SELECT
    cardnumber
FROM
    borrowers
WHERE
    dateexpiry BETWEEN <<Expires bewteen|date>> AND <<and|date>>

Patron Characteristics

Patrons with All Attribute Values

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: Patron list with the value of all of their custom patron attributes
Status: Complete

SELECT p.surname, p.firstname, p.cardnumber, a.code, a.attribute 
FROM borrowers p
LEFT JOIN borrower_attributes a USING (borrowernumber)
GROUP BY a.attribute 
ORDER BY p.surname, p.firstname ASC

Patrons with a Specific Attribute Code

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: Patron list with the value of one of their custom patron attributes (student id)
Status: Complete

SELECT p.surname, p.firstname, 
       p.cardnumber, a.attribute AS 'Attribute' 
FROM borrowers p
LEFT JOIN borrower_attributes a USING (borrowernumber) 
WHERE a.code = <<Attribute Code>>

Patrons with a Specific Attribute Value

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: Email list for patrons with a specific attribute value
Status: Complete

SELECT p.email
FROM borrowers p
LEFT JOIN borrower_attributes a USING (borrowernumber)
WHERE a.code = 'NEWSLETTER' AND a.attribute='1' AND p.email IS NOT NULL AND p.email != ''

Patrons with notes or messages

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: Patrons with notes and messages on their accounts
Status: Completed

SELECT b.cardnumber, b.surname, b.firstname,
b.opacnote, b.borrowernotes, group_concat(DISTINCT m.message separator ', ') AS circmesages
FROM borrowers b
LEFT JOIN messages m USING (borrowernumber)
WHERE b.branchcode=<<Branch|branches>> AND ((b.opacnote IS NOT
NULL AND b.opacnote != '') OR (b.borrowernotes IS NOT NULL AND
b.borrowernotes != '') OR (m.message IS NOT NULL AND
m.message != '')) GROUP BY b.borrowernumber ORDER BY b.surname ASC,
b.firstname ASC

Patron with messages but no email

Developer: Amy Boisvert, VOKAL
Module: Patrons
Purpose: Patrons with email addresses that do not have the patron messaging preference for holds checked.
Status: Completed

SELECT b.surname, b.firstname, b.cardnumber, b.email
FROM borrowers b
     LEFT JOIN (SELECT p.borrowernumber
                FROM borrower_message_preferences p 
                INNER JOIN borrower_message_transport_preferences t
                ON p.borrower_message_preference_id=t.borrower_message_preference_id
                WHERE p.message_attribute_id=4) e 
     ON b.borrowernumber=e.borrowernumber
WHERE b.branchcode=<<Your branch|branches>>
AND IFNULL(b.email,'') <>'' AND e.borrowernumber IS NULL

Search patron messages by keyword, with delete link

Developer: Owen Leonard, Athens County Public Libraries
Module: Patrons
Purpose: Returns a list of patrons who have messages with a specific keyword or keyword phrase. Includes a link to delete messages directly.
Status: Completed
Notes: The "Remove" link generated by this report will trigger the remove of the corresponding patron message WITHOUT CONFIRMATION.

SELECT CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowers.borrowernumber,'\">',borrowers.surname,', ',borrowers.firstname,'</a>') AS Name,
	messages.message,
    messages.message_date,
	CONCAT('<a href=\"/cgi-bin/koha/circ/del_message.pl?message_id=',messages.message_id,'&borrowernumber=',borrowers.borrowernumber,'\">Remove</a>') AS `Remove`
FROM borrowers, messages
WHERE borrowers.borrowernumber = messages.borrowernumber
	AND messages.message LIKE CONCAT( '%', <<Keyword phrase>>, '%')
ORDER BY messages.message_date

Borrower messaging preferences by branch

Developer: Caroline Cyr La Rose, inLibro (original by Barton Chittenden, ByWater Solutions)
Module: Patrons
Purpose: Show messaging preferences for all borrowers at a given branch
Status: Completed

SELECT 
    borrowers.cardnumber AS "Card number",
    CONCAT(borrowers.surname, ', ', borrowers.firstname) AS "Name",
    borrowers.email AS "Email",
    days_in_advance AS "Days in advance", 
    CASE wants_digest 
        WHEN 0 THEN ""
        WHEN 1 THEN "Digest only"
        ELSE wants_digest END AS "Digest only", 
    message_name AS "Message", 
    group_concat( DISTINCT borrower_message_transport_preferences.message_transport_type SEPARATOR ',') AS "Type"
FROM 
    borrower_message_transport_preferences
    JOIN borrower_message_preferences USING (borrower_message_preference_id)
    JOIN message_attributes USING (message_attribute_id)
    JOIN message_transports USING (message_attribute_id) 
    JOIN borrowers USING (borrowernumber)
WHERE
    borrowers.branchcode = <<Branch|branches>>
GROUP BY borrowernumber, message_name
ORDER BY borrowernumber

Patrons with modified messaging preferences

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Patrons
Purpose: Show patrons whose messaging preferences do not match the defaults for their category
Status: Completed

SELECT borrowernumber,
	CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">', 'click here', '</a>' ) AS link_to_borrower,
    firstname,
    surname,
    categorycode,
    bpg.pref_list AS patron_choice,
    cpg.pref_list AS category_defaults
FROM (
		SELECT borrowernumber,
			group_concat(bp.prefs ORDER BY LEFT(bp.prefs,1)) AS pref_list
		FROM 
			(SELECT borrowernumber,
            	concat(message_attribute_id, ifnull(days_in_advance,' '), wants_digest, group_concat(message_transport_type ORDER BY message_transport_type)) AS prefs
			FROM borrower_message_preferences
				LEFT JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
			WHERE borrowernumber IS NOT NULL
			GROUP BY borrower_message_preference_id) bp
		GROUP BY borrowernumber
	) bpg
	LEFT JOIN borrowers b USING (borrowernumber)
	LEFT JOIN (
				SELECT categorycode,
					group_concat(cp.prefs ORDER BY LEFT(cp.prefs,1)) AS pref_list
				FROM 
					(SELECT categorycode,
						concat(message_attribute_id, ifnull(days_in_advance,' '), wants_digest, group_concat(message_transport_type ORDER BY message_transport_type)) AS prefs
					FROM borrower_message_preferences
						LEFT JOIN borrower_message_transport_preferences USING (borrower_message_preference_id)
					WHERE categorycode IS NOT NULL
					GROUP BY borrower_message_preference_id) cp
				GROUP BY categorycode
                ) cpg USING (categorycode)
WHERE bpg.pref_list!=cpg.pref_list

Patron search by sort1

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: A search of patrons using the sort1 field that show checkouts and overdues
Status: Complete

SELECT CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',p.borrowernumber,'\">',p.surname, ', ', p.firstname,'</a>') AS patron, p.cardnumber, 
          REPLACE((SELECT count(c.itemnumber) FROM issues c WHERE p.borrowernumber=c.borrowernumber AND c.date_due >= now()),'0','') AS checkouts, 
          REPLACE(CONCAT('<div style=\"color:#f11\">',(SELECT count(i.itemnumber) FROM issues i WHERE p.borrowernumber=i.borrowernumber AND i.date_due < now()),'</div>'),'0','') AS overdues 
FROM borrowers p 
WHERE p.sort1=<<Sort 1 value>> 
ORDER BY p.surname, p.firstname

Guarantor List

Developer: Bernardo Gonzalez Kriegel
Module: Patrons
Purpose: A list of guarantors and guarantees
Status: Complete - only works on Koha 19.05 and earlier

SELECT 
  IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'') AS guarantor,
  IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'') AS guarantee
FROM
(  
  SELECT * 
  FROM borrowers
  WHERE guarantorid  IS NOT NULL
) AS p
LEFT JOIN borrowers AS g 
  ON p.guarantorid  = g.borrowernumber
ORDER BY g.borrowernumber

Ex Heavy Borrowers

Developer: Chris Hall for Horowhenua Library Trust, Catalyst
Module: Borrowers
Purpose: To show which borrowers, given a certain date range, have transformed from heavy borrowers to light borrowers. The report takes two ranges of dates, calculates the number of issues in each, and according to the thresholds set in the report, shows us borrowers who used to borrow a lot, but now don't borrow as much.
Status: Completed

SELECT first.borrowernumber, first.firstname, first.surname, first.cardnumber, issues_before, issues_after FROM(
SELECT borrowernumber, firstname, surname, cardnumber, count(old_issues.itemnumber) AS issues_before
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of first range (yyyy-mm-dd)|date>>
AND issuedate < <<End of first range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS first
JOIN (
SELECT borrowernumber, count(old_issues.itemnumber) AS issues_after
FROM borrowers JOIN old_issues
USING (borrowernumber)
WHERE issuedate > <<Beginning of second range (yyyy-mm-dd)|date>>
AND issuedate < <<End of second range (yyyy-mm-dd)|date>> 
GROUP BY old_issues.borrowernumber
) AS second
WHERE first.borrowernumber = second.borrowernumber
AND issues_after < 20 AND issues_before > 60 -- borrowers borrowed fewer than 20 items in the second range, and more than 60 in the first range.

Housebound planned deliveries - for choosers

Developer: PTFS Europe
Module: Circulation
Purpose: Lists all upcoming housebound deliveries with details of each recipient's preferences so that chooser's can pick relevant material.
Status: Completed

SELECT 
  housebound_visit.chooser_brwnumber AS "Chooser", 
  housebound_visit.appointment_date AS "Delivery date", 
  housebound_visit.day_segment AS "Time of day", 
  concat(borrowers.title, " ", borrowers.firstname, " ", borrowers.surname) AS "Recipient name", 
  borrowers.cardnumber AS "Card number", 
  housebound_profile.fav_itemtypes AS "Favourite types", 
  housebound_profile.fav_subjects AS "Favourite subjects", 
  housebound_profile.fav_authors AS "Favourite authors" 
FROM housebound_visit 
INNER JOIN borrowers ON borrowers.borrowernumber=housebound_visit.borrowernumber INNER JOIN housebound_profile ON housebound_profile.borrowernumber=housebound_visit.borrowernumber 
WHERE appointment_date > CURDATE()
ORDER BY housebound_visit.chooser_brwnumber ASC

Housebound Details for Choosers with Past Checkouts

Developer: ByWater Solutions
Module: Circulation
Purpose: Lists all upcoming housebound deliveries with details of each recipient's preferences so that chooser's can pick relevant material as well as past checkouts.
Status: Completed

SELECT
housebound_visit.chooser_brwnumber AS "Chooser",
housebound_visit.appointment_date AS "Delivery date",
housebound_visit.day_segment AS "Time of day",
concat(borrowers.title, " ", borrowers.firstname, " ", borrowers.surname) AS "Recipient name",
borrowers.cardnumber AS "Card number",
housebound_profile.fav_itemtypes AS "Favourite types",
housebound_profile.fav_subjects AS "Favourite subjects",
housebound_profile.fav_authors AS "Favourite authors",
group_concat(
concat('<b>',biblio.title, ifnull(biblio.subtitle,''),'</b>',' returned:',old_issues.returndate)
separator '<br>') AS past_checkouts
FROM housebound_visit
INNER JOIN borrowers ON borrowers.borrowernumber=housebound_visit.borrowernumber INNER JOIN housebound_profile ON housebound_profile.borrowernumber=housebound_visit.borrowernumber
LEFT JOIN old_issues ON (borrowers.borrowernumber=old_issues.borrowernumber AND date(old_issues.returndate) > date_sub(curdate(),interval 6 month))
LEFT JOIN items USING (itemnumber)
LEFT JOIN biblio USING (biblionumber)
GROUP BY housebound_visit.borrowernumber
ORDER BY housebound_visit.chooser_brwnumber ASC

Housebound planned deliveries - for deliverers

Developer: PTFS Europe
Module: Circulation
Purpose: Lists all upcoming housebound deliveries so that deliverers can plan their visits.
Status: Completed

SELECT 
   housebound_visit.deliverer_brwnumber AS "Deliverer", 
   housebound_visit.appointment_date AS "Delivery date",
   housebound_visit.day_segment AS "Time of day", 
   concat(borrowers.title, " ", borrowers.firstname, " ", borrowers.surname) AS "Recipient name", 
   borrowers.cardnumber AS "Card number", concat(borrowers.address, "\n", borrowers.city, "\n", borrowers.zipcode) AS Address 
FROM housebound_visit 
INNER JOIN borrowers ON borrowers.borrowernumber=housebound_visit.borrowernumber 
WHERE appointment_date > CURDATE()
ORDER BY housebound_visit.deliverer_brwnumber ASC

Enrollment Questions Answers for All Patrons in Club

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Patrons
Purpose: Shows answers provided at enrollment for all patrons in a given club
Status: Complete

SELECT borrowernumber, surname, firstname, club_id, clubs.name, club_template_enrollment_fields.name, club_enrollment_fields.value
FROM club_enrollment_fields
LEFT JOIN club_enrollments ON (club_enrollment_fields.club_enrollment_id=club_enrollments.id)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN clubs ON (club_enrollments.club_id=clubs.id)
LEFT JOIN club_template_enrollment_fields ON (club_enrollment_fields.club_template_enrollment_field_id=club_template_enrollment_fields.id)
WHERE clubs.id=<<Enter club ID number>> AND club_enrollments.date_canceled IS NULL

Borrower Relationships (19.11)

Developer: Kelly McElligott, ByWater Solutions
Module: Patrons
Purpose: Gives Borrower Relationship Information
Status: Complete

SELECT IFNULL(concat(e.surname, ', ', e.firstname, ' (',e.cardnumber, ')'),'') AS Guarantee,IFNULL(concat(r.surname, ', ', r.firstname, ' (',r.cardnumber, ')'),'') AS Guarantor, borrower_relationships.relationship
FROM borrower_relationships
LEFT JOIN borrowers e ON borrower_relationships.guarantee_id=e.borrowernumber
LEFT JOIN borrowers r ON borrower_relationships.guarantor_id=r.borrowernumber
ORDER BY guarantee_id

Show All Guarantors Per Guarantee

Developer: Andrew Fuerste-Henry, ByWater Solutions
Module: Patrons
Purpose: List all guarantors for each guarantee
Status: Complete

SELECT surname, firstname, cardnumber, group_concat(guar_info separator ' / ') AS patron_guarantors, concat(b.contactname,', ',b.contactfirstname,', ', b.relationship) AS non_patron_guarantor
FROM borrowers b 
LEFT JOIN borrower_relationships br ON (b.borrowernumber=br.guarantee_id) 
LEFT JOIN (SELECT borrowernumber, concat(surname,', ', firstname,', ', cardnumber) AS guar_info FROM borrowers) g ON (br.guarantor_id=g.borrowernumber)
GROUP BY b.borrowernumber
HAVING (patron_guarantors IS NOT NULL OR non_patron_guarantor IS NOT NULL)

Active Patrons by Sex

Developer: Spencer Smith
Module: Patrons
Purpose: Returns a count of patrons with activity in a certain date range, grouped by sex
Status: Complete

SELECT
    count(*) AS count,
    sex
FROM
    borrowers
WHERE
    borrowernumber IN (
        SELECT
            borrowernumber
        FROM
            statistics
        WHERE
             DATE( datetime ) BETWEEN <<Has activity BETWEEN (YYYY-MM-dd)|date>> AND <<and|date>>
    )
GROUP BY
    sex

Patrons Gone Wild

Missing Emails

Developer: Sharon Moreland
Module: Patrons
Purpose: Missing e-mails
Status: Complete

 SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry 
  FROM borrowers 
  WHERE ' ' IN (email)

Duplicate Patrons

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: List of patrons who are potentially duplicates
Status: Complete

SELECT surname, firstname, GROUP_CONCAT(cardnumber SEPARATOR ', ') AS barcodes,
GROUP_CONCAT(borrowernumber SEPARATOR ', ') AS borrowers 
FROM borrowers 
GROUP BY CONCAT(trim(surname),"/",trim(firstname),"/") 
HAVING COUNT(CONCAT(trim(surname),"/",trim(firstname),"/"))>1

Restricted Patrons

Developer: Nicole C. Baratta, ByWater Solutions
Module: Patrons
Purpose: List of patrons who have been marked as restricted
Status: Complete

SELECT cardnumber, surname, firstname,
       debarred, debarredcomment 
FROM borrowers 
WHERE branchcode=<<Select your branch|branches>> AND debarred IS NOT NULL
ORDER BY surname ASC, firstname ASC

Patron without image

Developer: Md. Zahid Hossain Shoeb, Independent University, Bangladesh (IUB)
Module: Patrons
Purpose: To list the patrons whose images have not been uploaded.
Status: Complete

SELECT cardnumber, borrowernumber, surname, firstname FROM borrowers 
WHERE borrowernumber
NOT IN (SELECT borrowernumber FROM patronimage)

Missing or invalid email format

Developer: Md. Zahid Hossain Shoeb, Independent University, Bangladesh (IUB)
Module: Patrons
Purpose: To list the patrons missing or invalid email format.
Status: Complete

SELECT surname AS Surname, firstname AS "First Name", cardnumber AS "Card Number", email AS Email
FROM borrowers
WHERE email NOT LIKE '%_@__%.__%'
ORDER BY email DESC

Heavily Indebted Patrons

Developer: Caroline Cyr La Rose, inLibro
Module: Accounts
Purpose: Shows patron info of patrons owing more than X $
Status: Completed

SELECT
  borrowers.cardnumber AS 'Card number',
  borrowers.surname AS 'Last name',
  borrowers.firstname AS 'First name',
  borrowers.phone AS 'Phone number',
  CONCAT(borrowers.streetnumber, ' ', borrowers.address, ' ', borrowers.address2, '<br>', borrowers.city, ' (', borrowers.state, ') ', borrowers.zipcode) AS 'Address',
  CONCAT(ROUND(SUM(accountlines.amountoutstanding), 2), ' $') AS 'Amount due',
  CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">See patron file</a>' ) AS ''
FROM accountlines
LEFT JOIN borrowers USING (borrowernumber)
GROUP BY borrowers.borrowernumber
HAVING SUM(accountlines.amountoutstanding) >= <<Owes more than>>

SQL Reports Patrons

From Koha Test Wiki MediaWiki Postgres

Contents

Patron Statistics

General

Export patrons

Patrons and Issues

Patrons with Checked Out Items

Patrons with No Checkouts

Anonymous Patron Account Report

Patrons and Age

Patron Birthday Report

Patrons of specific age range

Patrons Whose Age Violates Category Age Limits

Count of discharges, select date range

Count of Active Patrons by Age Group in a Specified Date Range

New Patrons

New Patron List (previous month)

New Patrons by Category at Branch

New Patrons by Category in Date Range

New Patron Count (previous month)

New Patron Count (by Branch/Category) (previous month)

New Patrons by Branch (year to date)

Count of New Patrons

Patrons and Branches

Patrons, Categories, and Permissions

Patrons with Staff Permissions

Superlibrarians

Patrons with staff permission, and if they are superlibrarians

Patron Permissions

Permissions Check

Active Patrons

List Patrons with the SEEN attribute

List Active Patrons

Count Active Patrons

Count of active patrons by category, filtered by date range

Count Active Patrons by Category for a Specific Month

List Active Patrons with over $20 in fines

Count of Renewed Memberships in Date Range

Renewed Memberships in Date Range with Patron Details

Patrons Who Borrowed More Than X Documents in Date Range

Count of Patrons by Category Who Borrowed More Than X Documents in Date Range

Patrons renewed within a date range

Expired/ Deleted Patrons

Count of Expired Patrons

Expired Patrons without Checkouts

Expired patrons with Checkouts

Expired Patrons and Funds Owed

Count of patrons whose cards will expire after a specific date, group by category

Count of deleted patrons

List of patrons not using the OPAC or SIP

List of patrons expired in date range

List of patrons expired in date range (just cardnumbers for batch modification)

Patron Characteristics

Patrons with All Attribute Values

Patrons with a Specific Attribute Code

Patrons with a Specific Attribute Value

Patrons with notes or messages

Patron with messages but no email

Search patron messages by keyword, with delete link

Borrower messaging preferences by branch

Patrons with modified messaging preferences

Patron search by sort1

Guarantor List

Ex Heavy Borrowers

Housebound planned deliveries - for choosers

Housebound Details for Choosers with Past Checkouts

Housebound planned deliveries - for deliverers

Enrollment Questions Answers for All Patrons in Club

Borrower Relationships (19.11)

Show All Guarantors Per Guarantee

Active Patrons by Sex

Patrons Gone Wild

Missing Emails

Duplicate Patrons

Restricted Patrons

Patron without image

Missing or invalid email format

Heavily Indebted Patrons

Views