Satish, On Sat, Feb 28, 2015 at 10:10 AM, SATISH <lis4satish@gmail.com> wrote:
I found one solution, but it is not through Koha. I exported unique records generated by both the item type BB and BK and put it in a single excel file and applied conditional formating and applied filter ( to ISBN column, since it is unique) by colour and found the duplicate biblionumber/titles generated between BB and BK (not among BB or BK) and removed manually. But, still I expect, automated software to perform this using sql.
I do not know the workflow at your library. But here is what I would do: 1/ Create two patron categories - a) NS - Regular Students b) RS - Reserved Students (the category codes and names are just place holders, you can call them whatever you want) NOTE: All students who do not require additional institutional / govt support are added under the first category; all students entitled to "Book Bank" use under the second one. 2/ Create ***three*** item types - a) RB (Regular Books) and b) BB (Bookbank Books) AND c) " BK" as Books 3/ Define the following Circulation rules for your library: a> Regular Student - Book Bank - 0 Checkouts - 0 loan period b> Regular Student - Regular Books - 2 Checkouts - 14 day loan period c> Reserved Student - Book Bank - 2 Checkouts - 14 day loan period d> Reserved Student - Regular Books - 2 Checkouts - 14 day loan period Note: adjust the number of allowed checkouts against itemtypes and the loan period as per your library policy 4/ Cataloging - Lets take the following example: We've 3 copies of the book "iGenetics" by "Peter J. Russell. Of which 2 copies may be issued to general students and 1 copy to be reserved for book bank use. While entering the bibliographic data (NOT your holdings data) enter "BK" (the third itemtype defined in this example) in the MARC21 field 942$c (recorded in biblioitems.itemtype). ___BUT___ when you add your ***actual*** items (against that particular ISBN / ISSN etc), make sure you appropriately flag the 952$y (recorded in items.itype) field as either "RB" or "BB" as is the case with the specific copy. So, you could have two copies of "iGenetic" by Peter J. Peters as "RB" and one copy marked as "BB" If you follow that above workflow, then the following SQL will give you the result you want: SELECT b.biblionumber AS "Biblio No.", b.title AS Title, b.author AS Author, t.editionstatement AS Edition, t.publishercode AS Publisher, t.isbn AS ISBN, count(i.itemnumber) AS "No of Copies" FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) WHERE i.itype='BB' OR i.itype='RB' GROUP BY b.biblionumber hope this helps -- Indranil Das Gupta Phone : +91-98300-20971 Blog : http://indradg.randomink.org/blog IRC : indradg on irc://irc.freenode.net Twitter : indradg -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=- Please exchange editable Office documents only in ODF Format. No other format is acceptable. Support Open Standards. For a free editor supporting ODF, please visit LibreOffice - http://www.documentfoundation.org