[Koha] Catalogue - SQL Report Help

[Indranil Das Gupta]

Satish,

On Sat, Feb 28, 2015 at 10:10 AM, SATISH <lis4satish at 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