SQL Reports in Koha

Serial received during the month with Title & Frequency

SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,

IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '',

SUBSTRING( biblioitems.marcxml,

LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,

LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19)))

AS FREQUENCY FROM serial, biblio,biblioitems

WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(publisheddate) = 01 AND YEAR(publisheddate)= 2011

ORDER BY serial.subscriptionid ASC

Accession Register

SELECT items.barcode, biblio.author, biblio.title, biblioitems.notes, biblioitems.isbn,biblioitems.place, biblioitems.publishercode,biblio.copyrightdate, biblioitems.pages, biblioitems.size,items.itemcallnumber, biblioitems.lccn,items.price

FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.barcode BETWEEN 'XXXXX' AND 'XXXXXX'

ORDER BY items.barcode ASC

Members List

SELECT borrowers.title,borrowers.firstname,borrowers.surname,borrowers.streetnumber,borrowers.address,borrowers.address2,borrowers.city,borrowers.zipcode,borrowers.email,borrowers.phone,borrowers.mobile FROM borrowers WHERE borrowers.branchcode='XX'

Fine Report

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname,

FORMAT(SUM(accountlines.amountoutstanding),2) as due

FROM borrowers LEFT JOIN accountlines ON

(borrowers.borrowernumber=accountlines.borrowernumber)

WHERE accountlines.amountoutstanding > 0

GROUP BY borrowers.cardnumber

ORDER BY borrowers.surname ASC

Serials detail with total issues information

SELECT serial.subscriptionid,serial.biblionumber,biblio.title, serial.planneddate,serial.publisheddate, subscriptionhistory.recievedlist,biblioitems.cn_item

FROM serial, biblio,subscriptionhistory,biblioitems

WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=subscriptionhistory.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(publisheddate) = 01 AND YEAR(publisheddate)= 2011

ORDER BY serial.subscriptionid ASC

Null Item List

SELECT items.barcode,items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,biblio.author,biblio.title, biblio.copyrightdate

FROM items

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

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

WHERE items.itype IS NULL AND items.homebranch='DBAD'

Shows the Missing serial List in particular month

SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,

IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '',

SUBSTRING( biblioitems.marcxml,

LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,

LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19)))

AS FREQUENCY FROM serial, biblio,biblioitems

WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(publisheddate) = 01 AND YEAR(publisheddate)= 2011 AND (status)=2

ORDER BY serial.subscriptionid ASC

missing/late/claimed serial during the month

SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate,

IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '',

SUBSTRING( biblioitems.marcxml,

LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19,

LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml,

LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19)))

AS FREQUENCY,serial.status FROM serial, biblio,biblioitems

WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(planneddate) = 02 AND YEAR(planneddate)= 2011 AND (status) BETWEEN '3' AND '5'

ORDER BY serial.subscriptionid ASC

Books transfer to various Branch

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

FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE DATE (items.dateaccessioned) BETWEEN '2011-07-01' AND'2011-07-31' AND items.homebranch='AG' AND items.itype='EN'

ORDER BY biblioitems.lccn ASC

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

FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE DATE (items.dateaccessioned) BETWEEN '2011-07-01' AND'2011-07-31' AND items.holdingbranch='AG' AND items.itype='EN'

ORDER BY biblioitems.lccn ASC

Subject Heading

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,items.barcode, biblio.title, biblio.author, ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")

FROM biblio_metadata

JOIN biblio USING (biblionumber)

JOIN items USING (biblionumber)

WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AND items.itype='EBK'

ORDER BY items.barcode ASC

Total Click count for E-books

SELECT count(url) AS 'times', biblio.author, biblio.title, linktracker.timeclicked, linktracker.biblionumber, linktracker.url

FROM linktracker, biblio

WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH) AND

linktracker.biblionumber = biblio.biblionumber

GROUP BY url

ORDER BY times DESC