.png)
Currently, I am working in a library which uses a single koha for various libraries. So, in course of time, I needed various reports specifically for my branch library. I started finding, creating, and modifying various SQL reports for my purpose. Following are those SQL reports that may be useful for those who work like me.
NB: Please, May I know other wanted branch-wise reports either created by you or from the official wiki.
Accession Register Report by Branch (from the drop-down list)
SELECT items.barcode,items.itemcallnumber,items.homebranch,items.itype,items.ccode,items.location,biblioitems.isbn,biblio.author,biblio.title,biblio.subtitle,biblioitems.editionstatement,biblioitems.place,biblioitems.publishercode,biblio.copyrightdate,biblioitems.pages,items.price,items.enumchron,items.dateaccessioned
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) where items.homebranch =<<Choose library|branches>>
ORDER BY items.barcode ASC
New Arrivals by Branch (from the drop-down list with all items)
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ''
AND i.homebranch = <<Branch|branches>>
GROUP BY biblionumber
HAVING isbn != ""
ORDER BY rand()
LIMIT 30
New Arrivals by Branch (directly with all items)
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ''
AND i.homebranch = 'EC'
GROUP BY biblionumber
HAVING isbn != ""
ORDER BY rand()
LIMIT 30
Here, "EC" is the code of my branch library, you may replace it with yours
New Arrivals by Branch (REFERENCE) only
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title, i.barcode, i.itype
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ''
AND i.itype = 'REF' AND i.homebranch = 'EC'
GROUP BY biblionumber
HAVING isbn != ""
ORDER BY rand()
LIMIT 30
Here, "REF" is the code we use for REFERENCE BOOKS "EC" is the code of my branch library, you may replace it with yours
New Arrivals by Branch (BOOK) only
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title, i.barcode, i.itype
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ''
AND i.itype = 'BK' AND i.homebranch = 'EC'
GROUP BY biblionumber
HAVING isbn != ""
ORDER BY rand()
LIMIT 30
Here, "BK" is the code we use for REFERENCE BOOKS "EC" is the code of my branch
Datewise List of Checked Out (Issued) Books by Branch (from the drop-down list)
SELECT DATE_FORMAT(c.issuedate, "%d %b %Y %h:%i %p") AS Issue_Date, DATE_FORMAT(c.date_due, "%d %b %Y") AS Due_Date,
i.barcode AS Barcode,
b.title AS Title,
b.author AS Author,
p.cardnumber AS Card_No,
p.firstname AS First_Name,
p.surname AS Last_Name,
i.homebranch
FROM issues c
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE c.issuedate
BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND i.homebranch = <<Branch|branches>>
ORDER BY c.issuedate DESC
Datewise List of Checked In (Returned) Books By Branch (from the drop-down list)
SELECT old_issues.returndate,items.barcode,biblio.title,biblio.author,borrowers.firstname,borrowers.surname,borrowers.cardnumber,borrowers.categorycode,items.homebranch
FROM old_issues
LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber
LEFT JOIN items ON old_issues.itemnumber=items.itemnumber
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
WHERE old_issues.returndate
BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND items.homebranch = <<Branch|branches>>
ORDER BY old_issues.returndate DESC
Overdue List By Branch (from drop-down list)
SELECT borrowers.surname,borrowers.firstname,issues.date_due, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', items.itemcallnumber, items.barcode,biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '30' AND items.homebranch = <<Branch|branches>>
ORDER BY borrowers.surname ASC, issues.date_due ASC
