[Koha] How to get sql report on patron additional attributes.

[Carlos Lopez]

Dear colleague

I assume that data appears in the " Additional attributes and identifiers" part of a borrower record. If that's the case, you likely have patron attributes set up for that information (Department, Student Roll number, blood group, etc.). 

We have some patron attributes set up to track what "organisation" a borrower is from and what "role" they exercise in that organisation. These are recorded in the borrower_attributes table, with a unique code for each type of data you're using them for (in our case the codes are "ORG" and "ROLE" and they map to specific lists of Authorised Values). 

The following report gives us a list of borrowers, and for each one we get borrower number, surname, firstname, description of the borrower category, expiry date, the organisation the borrower belongs to and the role they exercise. You might want to try something similar to this (please note that, because we want information for 2 different patron attributes, we call up the borrower_attributes table under 2 separate aliases so that we can ask for borrower_attribute.attribute by different borrower_attribute.code. If you want to pull Department, Student Roll number and blood group (and they are all stored in borrower_attributes) you'll likely need 3 aliases, each asking for attributes with a different code.

SELECT b.borrowernumber, b.surname, b.firstname,
c.description as "BORROWER TYPE", b.dateexpiry as "DATE EXPIRY",
ba1.attribute as "ORG", ba2.attribute as "ROLE"
FROM borrowers b
LEFT JOIN borrower_attributes ba1 on ba1.borrowernumber = b.borrowernumber and ba1.code = "ORG"
LEFT JOIN borrower_attributes ba2 on ba2.borrowernumber = b.borrowernumber and ba2.code = "ROLE" 
LEFT JOIN categories c on c.categorycode = b.categorycode
WHERE b.categorycode = <<Borrower category?|categorycode>>
order by b.surname, b.firstname

I hope this helps! 

With kind regards from the Dalton McCaughey Library Team

Carlos Lopez

Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library at dml.vic.edu.au | library.dmlibrary.org.au 

-----Original Message-----
From: Koha <koha-bounces at lists.katipo.co.nz> On Behalf Of MASTeR Library
Sent: Thursday, 16 March 2023 3:59 PM
To: koha <koha at lists.katipo.co.nz>; koha-request at lists.katipo.co.nz; koha-devel at lists.koha-community.org
Subject: [Koha] How to get sql report on patron additional attributes.

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


Dear Team,
how to get the sql report on Patron additional attributes for example
Department, Student Roll number, blood group and etc?

Sample SQL Code.
SELECT ROW_NUMBER() OVER (ORDER BY LPAD(borrowers.cardnumber,100,' ') ASC)
 AS 'S.No',
borrowers.cardnumber AS "Lib Membership
No",borrowers.borrowernumber,borrowers.surname AS "
Name",borrowers.title,borrowers.sex,borrowers.dateofbirth AS " Date of
Birth",borrowers.contactname AS "Guardian Name", borrowers.relationship,
borrowers.phone,borrowers.email,borrowers.address,borrowers.categorycode,borrowers.dateenrolled,borrowers.dateexpiry,borrowers.userid
FROM borrowers WHERE branchcode=<<Enter patrons library|branches>> AND
categorycode LIKE <<Select User category|categorycode>>
--
 Thanking you..

Librarian,
MAMCET <https://urldefense.com/v3/__http://mamcet.com/__;!!DVrgiXjqvl2yLjg!ed7jDm8AzcAFRxKjA0LeIvn7Ckox8by06f7g1LJQMXGUGZCEqGNXh4tYW-oSkiNcgqX-v_-EuT8g2McSUG_-mQatGQRK-dk$ >
Trichy-Chennai Trunk Road,
Siruganur, Tiruchirappalli-621 105.
_______________________________________________

Koha mailing list  https://urldefense.com/v3/__http://koha-community.org__;!!DVrgiXjqvl2yLjg!ed7jDm8AzcAFRxKjA0LeIvn7Ckox8by06f7g1LJQMXGUGZCEqGNXh4tYW-oSkiNcgqX-v_-EuT8g2McSUG_-mQatRcrByRU$
Koha at lists.katipo.co.nz
Unsubscribe: https://urldefense.com/v3/__https://lists.katipo.co.nz/mailman/listinfo/koha__;!!DVrgiXjqvl2yLjg!ed7jDm8AzcAFRxKjA0LeIvn7Ckox8by06f7g1LJQMXGUGZCEqGNXh4tYW-oSkiNcgqX-v_-EuT8g2McSUG_-mQatNc7yT94$