Branch-wise Reports (SQL) in Koha

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 = 'BS'

GROUP BY biblionumber

HAVING isbn != ""

ORDER BY rand()

LIMIT 30

Here, "BS" 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 = 'BS'

GROUP BY biblionumber

HAVING isbn != ""

ORDER BY rand()

LIMIT 30

Here, "REF" is the code we use for REFERENCE BOOKS "BS" 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 = 'BS'

GROUP BY biblionumber

HAVING isbn != ""

ORDER BY rand()

LIMIT 30

Here, "BK" is the code we use for REFERENCE BOOKS "BS" 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

REFERENCE

http://libtechnophile.blogspot.com/