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