Koha Reports

 Add the New Books in koha  Date wise List of Books

SELECT items.dateaccessioned,items.barcode,items.itemcallnumber,biblio.author,biblio.title,biblioitems.publishercode FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN

<<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>

ORDER BY items.barcode DESC

Accession Register Customize

SELECT items.Barcode,biblio.Author,biblio.Title AS Titles,biblioitems.Pages,biblioitems.Editionstatement AS Edition,biblio.Copyrightdate AS Year,items.Price,biblioitems.publishercode AS Publisher,biblioitems.Place,biblio.unititle AS Subject,items.location AS Department,biblioitems.itemtype AS Department,items.itype AS Status,biblioitems.url AS OnlineURL,items.Dateaccessioned,biblioitems.ISBN,biblio.notes,biblio.Serial,items.Issues,items.Renewals

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

ORDER BY LPAD(items.barcode,40,' ') ASC

How to Create Item Type wise Accession Register Report.

SELECT items.barcode AS Barcode,items.itemcallnumber AS 'Call Number',

CONCAT_WS('',biblio.author,'; ',ExtractValue

(metadata,'//datafield[@tag="700"]/subfield[@code="a"]')) AS Author,

CONCAT(biblio.title,' ',ExtractValue

(metadata,'//datafield[@tag="245"]/subfield[@code="b"]')) AS Title,

biblioitems.publishercode AS Publisher,biblioitems.Place,biblioitems.Pages,

biblioitems.Editionstatement AS Edition,biblio.Copyrightdate AS Year,items.Price,

biblio.unititle AS Subject,items.location AS Collection,

biblioitems.itemtype AS Department,items.itype AS 'Item Type',

biblioitems.url AS OnlineURL,items.Dateaccessioned As 'Date of Accessioned',

biblioitems.ISBN,biblio.notes,items.Issues,items.Renewals

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblio.biblionumber)

WHERE items.homebranch =<<Branch|branches>> AND items.itype=<<Item type|itemtypes>>

ORDER BY items.barcode ASC

 

Title List without copies

SELECT

b.title,

 b.author,

 t.editionstatement,

 t.publishercode,

 t.isbn,

 count(i.biblionumber) AS "Copies"

FROM biblio b

LEFT JOIN biblioitems t USING(biblionumber)

LEFT JOIN items i USING(biblionumber)

GROUP BY b.biblionumber

ORDER BY Copies ASC

 

Items with list of Collection Code

SELECT  items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate FROM items

LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) where items.homebranch =<<Enter patrons library|branches>> AND items.ccode LIKE <<Enter Collection Code|CCODE>>

 

Subject search (Search term from 650$a)

barcode,items.itemcallnumber,

title,author,editionstatement,EXTRACTVALUE(metadata,'/record/datafield[@tag="260"]/subfield[@code="c"]') AS Year,location
FROM items, SELECT  biblio,biblioitems,biblio_metadata
WHERE items.biblionumber = biblio.biblionumber and biblio.biblionumber = biblio_metadata.biblionumber  and  EXTRACTVALUE(metadata,'/record/datafield[@tag="650"]/subfield[@code="a"]') like <<Search Term (USE % AS wildcard)>> AND items.itype=<<Item type|itemtypes>>

 

 

 

 

 

Title List with keyword in Single/Branch Library in Title

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE homebranch = <<Pick your branch|branches>> AND biblio.title LIKE <<Title LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

Title List with keyword in all Libraries in Title

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE biblio.title LIKE <<Title LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

 

Call Number Search in Main Library

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE items.itemcallnumber LIKE <<Call number LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

 

Call Number Search in Single/Branch Library

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE homebranch = <<Pick your branch|branches>> AND items.itemcallnumber LIKE <<Call number LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

 

Bill Number Search in Library

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE items.stocknumber LIKE <<Bill Number LIKE (USE % FOR wildcard)>>

ORDER BY items.stocknumber ASC

 

List of Books in the Department Library search with Library Code

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE homebranch LIKE <<Call number LIKE (USE % FOR wildcard)>>

ORDER BY items.stocknumber ASC

 

List of Books Purchased by Department Code

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate,homebranch

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE items.ccode LIKE <<Department Code LIKE (USE % FOR wildcard)>>

ORDER BY items.stocknumber ASC

 

 

Title List with keyword in Single/Branch Library in Title

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE homebranch = <<Pick your branch|branches>> AND biblio.title LIKE <<Title LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

Title List with keyword in all Libraries in Title

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE biblio.title LIKE <<Title LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

 

Call Number Search in Main Library

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE items.itemcallnumber LIKE <<Call number LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

 

Call Number Search in Single/Branch Library

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE homebranch = <<Pick your branch|branches>> AND items.itemcallnumber LIKE <<Call number LIKE (USE % FOR wildcard)>>

ORDER BY items.itemcallnumber ASC

 

Bill Number Search in Library

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE items.stocknumber LIKE <<Bill Number LIKE (USE % FOR wildcard)>>

ORDER BY items.stocknumber ASC

 

List of Books in the Department Library search with Library Code

SELECT items.barcode,items.

dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate

FROM items

LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)

LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE homebranch LIKE <<Call number LIKE (USE % FOR wildcard)>>

ORDER BY items.stocknumber ASC

 

Highest No.of. Book's Readers Name List.

·                     Crete the New SQL Report

·                     Copy and Paste  following lines in the SQL report. Save and close.

SELECT

    cardnumber as 'Lib Card No',

    surname as Name,

    borrowernotes as Department,

   COUNT(*) as Reading

FROM

   borrowers b

   JOIN statistics s ON (b.borrowernumber = s.borrowernumber)

   WHERE DATE(datetime) between <<From|date>> and <<Until|date>>

GROUP BY b.borrowernumber

ORDER BY Reading DESC