SQL REPORTS

-

 

SQL REPORTS

 1. Accession register


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)

ORDER BY items.barcode ASC


2. Accession number sorted by barcode number


SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers,

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 =<<Branch|branches>>

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


3. Accession register with keyboard/subject


SELECT items.barcode, items.dateaccessioned, items.itemcallnumber, biblio.author, biblio.title, ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS Keyword,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)

JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)

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


4. Accession register with price


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

FROM items

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

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

ORDER BY items.barcode ASC


5. Accession/barcode number search


SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, 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 =<<Branch|branches>> AND items.barcode LIKE <<Enter Barcode>>

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


6. Catalogue by itemtype


SELECT COALESCE(homebranch,'*GRAND TOTAL*') AS homebranch,

IFNULL(itype, "") AS itype, count(itype) AS count

FROM items

WHERE dateaccessioned < <<Added before (yyyy-mm-dd)|date>>

GROUP BY homebranch, itype

WITH rollup


7. Check-in List of Books (Date wise)


SELECT old_issues.returndate,items.barcode,biblio.title,biblio.author,borrowers.firstname,borrowers.surname,borrowers.cardnumber,borrowers.categorycode 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>> ORDER BY old_issues.returndate DESC


8. Circulation Report by Date


SELECT issues.issuedate,items.barcode,biblio.title,author,borrowers.firstname,borrowers.surname

FROM issues

LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber

LEFT JOIN items ON issues.itemnumber=items.itemnumber

LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber

WHERE issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> ORDER BY issues.issuedate


9. Circulation-All Checked Out Books


SELECT issues.issuedate, issues.date_due, borrowers.categorycode, borrowers.surname, borrowers.firstname,

borrowers.phone, borrowers.email, biblio.title, biblio.author,

items.itemcallnumber, items.barcode, items.location

FROM issues

LEFT JOIN items ON (issues.itemnumber=items.itemnumber)

LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber)

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

WHERE issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> ORDER BY issues.issuedate


10. 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


11. Date Wise List of Checked Out Books


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

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>> ORDER BY c.issuedate DESC


12. Duplicate titles (using title and ISBN)


SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title,

b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns

FROM biblio b

LEFT JOIN biblioitems i

ON (i.biblionumber=b.biblionumber)

GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn)

HAVING COUNT(CONCAT(substr(b.title,0,9),"/",i.isbn))>1


13. Exporting of checkout entries


SELECT issues.issuedate,borrowers.cardnumber,items.barcode

FROM issues

LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber

LEFT JOIN items ON issues.itemnumber=items.itemnumber

LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber

ORDER BY issues.issuedate DESC


14. Highest No.of. Book's Readers


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


15. Items currently Checked out


SELECT issues.issuedate,issues.date_due,items.barcode,biblio.title, author,borrowers.firstname,borrowers.surname,borrowers.cardnumber FROM issues LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber LEFT JOIN items ON issues.itemnumber=items.itemnumber LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber ORDER BY issues.issuedate DESCItems currently Checked out


16. 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 ='SMWCTE' AND items.ccode LIKE 'FIC'


17. List of Seials


SELECT

CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,

CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,

biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) AS ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings

FROM

subscription

JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )

JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )

LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )

WHERE

(subscription.closed = 0)

GROUP BY biblio.biblionumber

ORDER BY biblio.title


18. Null Barcodes


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

JOIN items USING(biblionumber)

WHERE (items.barcode IS NULL OR items.barcode = '')


19. Overdue 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' ORDER BY borrowers.surname ASC, issues.date_due ASC


20. patron full list


SELECT borrowers.borrowernumber,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.title,borrowers.othernames,borrowers.initials,borrowers.streetnumber,borrowers.streettype,borrowers.address,borrowers.address2,borrowers.city,borrowers.state,borrowers.zipcode,borrowers.country,borrowers.email,borrowers.phone,borrowers.mobile,borrowers.fax,borrowers.emailpro,borrowers.phonepro,borrowers.B_streetnumber,borrowers.B_streettype,borrowers.B_address,borrowers.B_address2,borrowers.B_city,borrowers.B_state,borrowers.B_zipcode,borrowers.B_country,borrowers.B_email,borrowers.B_phone,borrowers.dateofbirth,borrowers.branchcode,borrowers.categorycode,borrowers.dateenrolled,borrowers.dateexpiry,borrowers.date_renewed,borrowers.gonenoaddress,borrowers.lost,borrowers.debarred,borrowers.debarredcomment,borrowers.contactname,borrowers.contactfirstname,borrowers.contacttitle,borrowers.borrowernotes,borrowers.relationship,borrowers.sex,borrowers.password,borrowers.flags,borrowers.userid,borrowers.opacnote,borrowers.contactnote,borrowers.sort1,borrowers.sort2,borrowers.altcontactfirstname,borrowers.altcontactsurname,borrowers.altcontactaddress1,borrowers.altcontactaddress2,borrowers.altcontactaddress3,borrowers.altcontactstate,borrowers.altcontactzipcode,borrowers.altcontactcountry,borrowers.altcontactphone,borrowers.smsalertnumber,borrowers.sms_provider_id,borrowers.privacy,borrowers.privacy_guarantor_fines,borrowers.privacy_guarantor_checkouts,borrowers.checkprevcheckout,borrowers.updated_on,borrowers.lastseen,borrowers.lang,borrowers.login_attempts,borrowers.overdrive_auth_token,borrowers.anonymized,borrowers.autorenew_checkouts FROM borrowers


21. Patron List by Category


SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.initials,borrowers.dateenrolled FROM borrowers WHERE branchcode=<<Enter patrons library|branches>> AND categorycode LIKE <<Enter Category borrowers|categorycode>>


22. Patron with Fine


SELECT

(SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>')

FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron,

format(sum(amountoutstanding),2) AS 'Outstanding',

(SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'

FROM

accountlines a, borrowers b

WHERE

(SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber) > '0.00'

AND a.borrowernumber = b.borrowernumber

GROUP BY

a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC


23. patron with fine date range


SELECT

datetime AS "Date",

cardnumber AS "Card number",

categorycode AS "Category code",

surname AS "Last name",

firstname AS "First name",

CASE type

WHEN 'issue' THEN "Check out"

WHEN 'localuse' THEN "In house use"

WHEN 'return' THEN "Check in"

WHEN 'renew' THEN "Renew"

WHEN 'writeoff' THEN "Amnesty"

WHEN 'payment' THEN "Payment"

ELSE "Other" END

AS "Transaction",

CASE value

WHEN '0' THEN "-"

ELSE value END

AS "Amount",

barcode AS "Barcode",

biblio.title AS "Title",

author AS "Author",

items.homebranch,

items.holdingbranch

FROM statistics

JOIN borrowers ON statistics.borrowernumber=borrowers.borrowernumber

LEFT JOIN items ON statistics.itemnumber=items.itemnumber

LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber

WHERE DATE (statistics.datetime) BETWEEN <<From Date|date>> AND <<To Date|date>>


24. Records without items


SELECT b.title AS Title,

CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value),

CONCAT('http://', systempreferences.value), ''), '/cgi-bin/koha/opac-detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS OPAC,

CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS Edit

FROM systempreferences, biblio AS b

LEFT JOIN items AS i ON b.biblionumber = i.biblionumber

WHERE i.itemnumber IS NULL AND

systempreferences.variable='OPACBaseURL'


25. Report Between Accession Numbers


SELECT CONCAT('<a href=\"/cgi-bin/koha/


catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, 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 =<<Branch|branches>> AND items.barcode BETWEEN <<From Acc. No.>> AND <<To Acc. No.)>>

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


26. Report with volumes and titles


SELECT homebranch, items.itype, itemtypes.description, count(DISTINCT items.biblionumber) AS bibs,

count(items.itemnumber) AS items

FROM items, itemtypes

WHERE items.itype=itemtypes.itemtype AND items.barcode IS NOT NULL

GROUP BY items.itype

ORDER BY itemtypes.description


27. Total fines & fees, payments made, the outstanding, written off and forgiven amounts between a specified date range


SELECT * FROM


(SELECT (@FromDate:=<<From date|date>>) AS 'From (y-m-d)', (@ToDate:=<<To date|date>>) AS 'To (y-m-d)') AS T1,


(SELECT

IFNULL(ROUND(SUM(accountlines.amount), 2), "0.00") AS 'Total Fines/Fees' FROM accountlines

WHERE

accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T2,


(SELECT

IFNULL(ROUND(SUM(accountlines.amountoutstanding), 2), "0.00") AS 'Total O/S' FROM accountlines

WHERE

accounttype IN ('F', 'FU', 'N', 'A', 'M', 'L') AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T3,


(SELECT

IFNULL(REPLACE(ROUND(SUM(amount),2),"-",""), "0.00") AS 'Paid / Credited' FROM accountlines

WHERE

accounttype IN ('PAY', 'C') AND description NOT LIKE "%Reversed%" AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T4,


(SELECT

IFNULL(REPLACE(ROUND(SUM(amount),2),"-",""),"0.00") AS 'Written off' FROM accountlines

WHERE

accounttype='W' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T5,


(SELECT

IFNULL(REPLACE(ROUND(SUM(amount),2), "-", ""), "0.00") AS 'Forgiven' FROM accountlines

WHERE

accounttype='FOR' AND DATE(timestamp) BETWEEN @FromDate AND @ToDate) AS T6


27. Total Price of books


SELECT items.price,items.replacementprice,biblio.title,biblio.author,items.itemcallnumber

FROM items

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

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

WHERE items.homebranch=<<Home branch|branches>>

ORDER BY items.itemcallnumber ASC


28. Total Record count


SELECT COUNT(biblionumber) AS Count FROM biblio


29. Total stock


SELECT items.barcode,items.itemcallnumber,biblio.title FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)

 

30. Report for creating Spine labels

 

SELECT ExtractValue(metadata,'//datafield[@tag="082"]/subfield[@code="a"]') AS ClassNo, ExtractValue(metadata,'//datafield[@tag="082"]/subfield[@code="b"]') AS BookNo,items.barcode
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
WHERE items.dateaccessioned


31. Date wise Catalogued 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

Comments

Post a Comment

Popular posts from this blog

BASIC LINUX COMMANDS

-
Image
  Display information about the Operating  System hostnamectl   Display information about the CPU architecture lscpu   Listing files and directories   ls   Detailed listing   ls -l   Lots of details about the contents of your directory   The * wildcard   ls d*   This will list all files in the current directory starting with d   Making Directories   mkdir tom   Changing to a different directory   cd tom   Change to the parent directory   cd ..   will take you one directory up the hierarchy. Command Meaning ls List files and directories ls -a List all files and directories mkdir Make directory cd directory Change directory cd Change to the home directory cd ~ Change to the home directory...
continue reading »

Installing EPrints on Debian/Ubuntu

-
Image
Requirements Here are the procedures to install EPrints 3.4.x on Ubuntu 16.04 LTS. Ubuntu   18.04 LTS   Ubuntu   20.04 LTS   or  Debian 9 (Stretch) or 10 EPrints wrote on PERL.   Installation requirements are Linux (Debian/Ubuntu/Red Hat), MySQL and Apache.  Here is the step by step instructions; 1. root user sudo su 2. Update the Ubuntu apt update apt upgrade 3. Install a simple and lightweight text editor  apt install leafpad or  apt-get install gedit 4. Add the EPrints repository to Ubuntu. Open the sources list file, leafpad /etc/apt/sources.list or  gedit /etc/apt/sources.list Copy and paste the following lines into the bottom of the file, #EPrints repository deb http://deb.eprints.org/stable ./ Save and close the file. Add EPrints repository key, wget -O - http://deb.eprints.org/keyFile | apt-key add - Update the repository index, apt update 5. Install EPrints apt install eprints The installation process will ask to create Roo...
continue reading »

Install Zotero using Snap package management tool

-
Image
  Install Zotero using Snap package management tool Image courtesy:  https://snapcraft.io Zotero reference management tool has no Debian/Redhat package for installation. Users had to install from source. Installation from source is not easy for Linux newbies. Now  Zotero snaps  packaging available. Snap  is a software package management and deployment system developed by Canonical (Mother organisation of Ubuntu Linux). It's easy to deploy applications using the Snap tool. Snap package management system can work with most Linux distributions (e.g. Debian, Ubuntu, Fedora, Arch). Packages are called ' snaps ' and the package management tool called ' snapd '. Linux users have to install the snapd tool in Linux to install applications in the snap repository. Install Snap  sudo apt update sudo apt install snapd Restart the computer after the installation of snapd. Install Zotero using snap Zotero is a free, easy-to-use tool to help you collect, organise, cite, an...
continue reading »