[Koha] Catalogue - SQL Report Help

[SATISH]

Thank you very much Paul, Nick and Indranil,
for your patience reading of my previous posts and valuable and detailed
explanations with examples.  I got complete solutions in the context which
I was looking for.. from your answers. Just to accommodate searching within
item type in advanced search, I was using item type at bibliographic record
level (942$c) not at item level (952$y) previously and it has triggered
problems at report generation. And I admit, I was unaware of 952$y field
and assumed that whatever is in 942$c is same as 952$y (of course, it uses
the same info)  and thought 942$c is re-appearing again at the item level
and did not modify anything further.

Thank you again for this forum for helping.


With thanks
Satish MV
Librarian
Govt. Engineering College, Hassan.
Karnataka.





On Sun, Mar 1, 2015 at 12:07 AM, Indranil Das Gupta <indradg at gmail.com>
wrote:

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



--