Hi, 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. When fetching unique records from particular item type is possible means, merging both the items types results, creating a view for that and eliminating duplicate records should also be possible using sql. But I am not good in mysql. Since my library requirement is to maintain "book" and "book bank" as separate item types for accommodating more than one circulation rules and also facilitate searching with-in particular item type. I request this forum to help me for doing this within koha using sql not manually (using excel) please or someone already did similar exercise please help me. Hoping to get some solution. With thanks. Satish MV Librarian Govt. Engineering College, Hassan. Karnataka. On Fri, Feb 27, 2015 at 11:06 AM, SATISH <lis4satish@gmail.com> wrote:

Thank you Paul, I made corrections, but the purpose is not solved. By using "OR" it populates duplicate record.

By maintaining only one kind of item type, I can not make varied circulation policies (but can generate unique records report for books), and in this case, for the same user I have to provide two different kind of ID cards for issue books from BK and BB, since no provision to create different circulation policy.

And if I make two kinds of Item Type, then unique report won't come ? (can make varied circulation policies, that is, by using single ID, I can issue books of different item types).

Is there any way to generate a report either separately for each item type or as indicated by you using "OR" between BK/BB and then apply some query to eliminate duplicates within Koha.

or any other way, koha team could help me ??

Hoping to get some solution.

Thank You Satish MV Librarian Govt. Engineering College, Hassan. Karnataka.

On Thu, Feb 26, 2015 at 8:04 PM, Paul A <paul.a@navalmarinearchive.com> wrote:

...

At 11:00 AM 2/26/2015 +0530, SATISH wrote: [snip]

...

And am looking for help for generating unique records (biblio) after merging both BK and BB. I am trying with following sql, but throwing errors. Can you please help me over here. ------------------------------------------------------------ ----------------------------------------------------------------

SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement, t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) WHERE items.itype='BB' AND items.itype='BK' GROUP BY b.biblionumber ---------------------------------------------------------------------

Error: The database returned the following error: Unknown column 'items.itype' in 'where clause'

Two points: First, after you define "JOIN items i" you refer to 'items' as 'i'. Second, itype cannot have two values -- it is either BB or BK... Try your last line as:

WHERE i.itype='BB' OR i.itype='BK' GROUP BY b.biblionumber;

Best -- Paul _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha

--

--

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@gmail.com> wrote:

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

--