Re: [Koha] SQL report

Hey Igor, Try this: SELECT itemnumber, biblionumber, barcode, stocknumber, withdrawn, itemnotes_nonpublic, itype FROM items WHERE stocknumber REGEXP '^[0-9]{4}$' AND itype = "BK"; Beste Grüße, Eugen Bastron > Am 20.02.2025 um 10:11 schrieb Сычев Игорь Алексеевич : > > Hi! > > How do I find

Re: [Koha] SQL report for URL

Visit item type wise collection SQL report https://libpowertech.blogspot.com/2019/05/department-wise-number-of-books-in-your.html?m=1 On Thu, 20 Jul 2023, 2:32 pm Dr. Prajeesh Bhaskaran, wrote: > Hi > Please, can anyone help me to create a SQL report for getting the URL of > all the items based

Re: [Koha] SQL Report of Recently Edited Records

The following will show the last 30 changed biblio records and is ready if you wish to add more conditions on other related tables. It can also be used as a coverflow datasource. SELECT b.biblionumber, i.Barcode, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title, c.imagenumber AS localcover F

Re: [Koha] SQL report to search for series statements and volume numbers

Hi Roger This works exactly as it is needed, and even with correct sorting! Great! I'm not really familiar with the UNION clause but this seems a great way of using it. The SQL statement may be somewhat inefficient but is very effective though. In our database with around 30'000 test records

Re: [Koha] SQL report to search for series statements and volume numbers

Hi Katrin You wrote: > if you use > > ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]') = > <> > > and there are multiple 490$a, they will appear as one space separated > string. That means, that you need to use LIKE and truncation with % to > locate the ones with the entry yo

Re: [Koha] SQL report to search for series statements and volume numbers

Hi Michael, an inefficient and quite long running SQL statement for your request might be the following: SELECT s.ser AS series, s.vol AS volume, s.biblionumber FROM ( SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][1]/subfield[@code="a"]') AS ser,

Re: [Koha] SQL report to search for series statements and volume numbers

Hi Michael, if you use ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]') = <> and there are multiple 490$a, they will appear as one space separated string. That means, that you need to use LIKE and truncation with % to locate the ones with the entry you are looking for. If y

Re: [Koha] SQL Report Coding need.

need someone who really > understands SQL. Forwarding to the list. > Elaine > VWML <https://vwml.org> > > > > -- Forwarded message - > From: MAMCET Library > Date: Sat, Jan 16, 2021 at 7:30 PM > Subject: Re: [Koha] SQL Report Coding need. >

Re: [Koha] -SQL report on total cost of books

There are a couple here that may be useful, at least for your first question. Bob https://wiki.koha-community.org/wiki/SQL_Reports_Library#Library_Books_Investment_by_Collection_Code On Mon, 9 Mar 2020 at 17:33, Yatheesh lis wrote: > Hi > > I would like to know total cost of books in my librar

Re: [Koha] SQL Report - Authorities. List duplicate values based on 010$a

e proper method for up-gradation of koha-latest > > > > With regards, > > Vijay Kumar > > > > > > error > > > > vijay@debian:/usr/share/koha-18.11.04$ sudo make upgrade > > Can't locate C4/Installer/UpgradeBackup.pm in @INC (you may need to &

Re: [Koha] SQL Report - Authorities. List duplicate values based on 010$a

/usr/lib/x86_64-linux-gnu/perl/5.24 /usr/share/perl/5.24 > /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base). > BEGIN failed--compilation aborted. > Makefile:22908: recipe for target 'make_upgrade_backup' failed > make: *** [make_upgrade_backup] Error 2 > >

Re: [Koha] SQL Report - Authorities. List duplicate values based on 010$a

Hi Craig. I'm not that great at reports, but you might be able to find something in the SQL reports library that could help: https://wiki.koha-community.org/wiki/SQL_Reports_Library David Nind | david.n...@gmail.com PO Box 12367, Thorndon, Wellington, New Zealand 6144 m. +64 21 0537 847 On Sun

Re: [Koha] SQL Report not populating

o.co.nz] On Behalf Of Barry, > Michael M > Sent: Wednesday, 13 June 2018 9:07 AM > To: koha > Subject: Re: [Koha] SQL Report not populating > > Hi Mark, > > I tried running a different saved report (made with the report wizard, to > produce a list of patrons by categorycod

Re: [Koha] SQL Report not populating

sage- From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Barry, Michael M Sent: Wednesday, 13 June 2018 9:07 AM To: koha Subject: Re: [Koha] SQL Report not populating Hi Mark, I tried running a different saved report (made with the report wizard, to produce a list of patrons by ca

Re: [Koha] SQL Report not populating

-boun...@lists.katipo.co.nz] On Behalf Of Mark Alexander Sent: Wednesday, 13 June 2018 1:11 AM To: koha Subject: Re: [Koha] SQL Report not populating I tried your query with the WHERE clause slightly simplified to give a non-empty result on my test installation of 18.05: WHERE (TO_DAYS(cu

Re: [Koha] SQL report with Comma Delimited Paramater

Yes, that's the one. Thanks! On Tue, Jun 12, 2018 at 7:31 AM, Magnus Enger wrote: > On 6 June 2018 at 15:17, Barton Chittenden > wrote: > > This is not currently possible, but if you check the bywatersolutions > > github page, you can find a plugin called 'koha-plugin-reports-plus', or > > some

Re: [Koha] SQL Report not populating

I tried your query with the WHERE clause slightly simplified to give a non-empty result on my test installation of 18.05: WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '' I was then able to download the CSV file and read it into LibreOffice Calc with no problems. So your query looks good; ma

Re: [Koha] SQL report with Comma Delimited Paramater

On 6 June 2018 at 15:17, Barton Chittenden wrote: > This is not currently possible, but if you check the bywatersolutions > github page, you can find a plugin called 'koha-plugin-reports-plus', or > something similar (on mobile, or I'd link to it). That plugin will allow > you to add a parameter t

Re: [Koha] SQL Report not populating

Hi Michael, I don't see an obvious reason why it would not work. Which version of Koha are you using? Katrin On 08.06.2018 01:12, Barry, Michael M wrote: Hi everyone, I'm having some issues downloading the results of SQL reports. Running Koha 18.05. I'm using the "overdues by homeroom" c

Re: [Koha] SQL report with Comma Delimited Paramater

This is not currently possible, but if you check the bywatersolutions github page, you can find a plugin called 'koha-plugin-reports-plus', or something similar (on mobile, or I'd link to it). That plugin will allow you to add a parameter that lets you enter a list. On Wed, Jun 6, 2018, 07:38 ksor

Re: [Koha] SQL report for list of authorities with occurrences

Dear Andreas, THANK YOU! It works perfectly for me! All best, Caterina De : Andreas Roussos Envoyé : mercredi 30 mai 2018 19:18:11 À : Caterina Sansone Cc : koha@lists.katipo.co.nz Objet : Re: [Koha] SQL report for list of authorities with occurrences Dear

Re: [Koha] SQL report for list of authorities with occurrences

Dear Caterina, Try pasting the following SQL into a Koha report, it should give you the number of occurrences per authority. Please bear in mind that I made the following assumptions: (a) your Topical Subject authorities go in UNIMARC field 606 of your biblios (b) you have no more than 5 subjects

Re: [Koha] SQL report for list of authorities with occurrences

ndreas Roussos Envoyé : mercredi 30 mai 2018 11:35:19 À : Caterina Sansone Cc : koha@lists.katipo.co.nz Objet : Re: [Koha] SQL report for list of authorities with occurrences Dear Caterina, When you say "clean the indexes", do you mean finding _unused_ subject authority records and rem

Re: [Koha] SQL report for list of authorities with occurrences

Dear Caterina, When you say "clean the indexes", do you mean finding _unused_ subject authority records and removing them? If the answer is "yes", have a look at the following thread: https://lists.katipo.co.nz/pipermail/koha/2017-November/049404.html On the other hand, if your intention is to co

Re: [Koha] SQL report

> Give privilege to reports by user types? If you mean by this that you'd like to limit some users' access to reports based on their patron category, it's not possible. I'd love to see a feature like that added (perhaps with permissions settings instead). -- Owen -- Web Developer Athens Count

Re: [Koha] SQL Report: List of all authority records created

Hi Jane and Stefano, Many thanks for your help! It is exactly what I was looking for ;). Best regards, Vanda 2017-03-09 2:29 GMT+00:00 Jane Cothron : > Here's one that we got from ByWater: > select authid, datecreated, > ExtractValue( marcxml, '//datafield[@tag="100"]/*') AS Personal_Name, > E

Re: [Koha] SQL Report: List of all authority records created

Hi, Vanda, look at https://wiki.koha-community.org/wiki/SQL_Reports_Library#Authorities_records_added.2Fdeleted_in_time_frame or try this (from March 1 to now): SELECT `authid`, authtypec

Re: [Koha] SQL report - Date issues

ramirou...@gmail.com] Sent: Wednesday, March 8, 2017 10:54 PM To: Kerrie Stevens Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] SQL report - Date issues Try this: SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml FROM biblioitems JOIN biblio ON biblioitems.biblionumb

Re: [Koha] SQL report - Date issues

Try this: SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml FROM biblioitems JOIN biblio ON biblioitems.biblionumber=biblio.biblionumber WHERE ExtractValue( marcxml, '//datafield[@tag="505"]/*' ) = "" and biblio.copyrightdate is null and ExtractValue( marcxml, '//datafield

Re: [Koha] SQL report for holds waiting for pickup longer than the ReservesMaxPickupDelay number of days

Jessica and Liz, Thanks a lot :) That's exactly what I was looking for. It was a great help! Regards, Vanda 2017-03-01 16:53 GMT+00:00 Tobin, Jessica L : > Hi Vanda, > We run this report daily to keep up with holds that are languishing > on the pick-up shelves for too long: > > > SELECT b

Re: [Koha] SQL report for holds waiting for pickup longer than the ReservesMaxPickupDelay number of days

Hi Vanda, We run this report daily to keep up with holds that are languishing on the pick-up shelves for too long: SELECT biblio.title AS 'title', p.surname, p.firstname AS 'first name', p.email, p.cardnumber AS 'patron barcode', h.waitingdate AS 'hold available date', i.barcode AS 'item

Re: [Koha] SQL report for holds waiting for pickup longer than the ReservesMaxPickupDelay number of days

Hi, In the Koha interface, you can go to Circulation -> Holds awaiting pickup and have a look at the "Hold(s) over" tab. This should show you the information you are wanting. Cheers, Liz Rea Catalyst IT On 01/03/17 23:16, vanda koha wrote: > Hi everybody, > > I would like to run a report that w

Re: [Koha] SQL Report-Circulation-- Help

Hi Pandu Rangaswamy, hope this sql helps your requirements - SELECT DISTINCT b.biblionumber, b.title, b.author, t.publishercode AS p

Re: [Koha] SQL Report-Circulation-- Help

Dear All, Currently i am using Koha version 3.12.10.000. and i am having two branch libraries. I am looking in SQL report to generate the books details with the following details. 1. Title of the book 2. Author 3. Publisher 4. Place of Publication and year 5. Name of the vendor 6. Invoice No and

Re: [Koha] SQL Report-Circulation-- Help

Hi Jonathan, I did a mistake with WHERE Coditions, like; p.categorycode=<> INSTEAD OF borrowers.categorycode (http://schema.koha-community.org/16_05/tables/old_issues.html ) and this was throwing error : Unknown column 'p.categorycode' in 'where clause' Now, I have corrected my query with the sy

Re: [Koha] SQL Report-Circulation-- Help

What is the error you get? Please answer to the list. On Wed, 22 Feb 2017 at 12:12 SATISH wrote: > thank you Jonathan for correcting sql syntax. > but after corrections,it is still showing error! > > with thanks > satish > > On 22 February 2017 at 15:57, Jonathan Druart < > jonathan.dru...@bugs

Re: [Koha] SQL Report-Circulation-- Help

Hello Satish, To add conditions to a SQL query you just need to separate them with the "AND" keyword, do not repeat "WHERE". So it's: SELECT fields FROM TABLE WHERE condition_a AND condition_b AND condition_c Regards, Jonathan On Wed, 22 Feb 2017 at 11:05 SATISH wrote: > Hi, > > With Ko

Re: [Koha] SQL report help

Kerrie, Are you just wanting count of items per collection code at a set branch? And no other information displayed? If so, try SELECT items.ccode, count(items.itemnumber) FROM items WHERE items.homebranch=<> GROUP BY items.ccode That should get you what you are you looking for, if I'm understan

Re: [Koha] SQL Report help

Thanks so much, was exactly what I needed. Final solution: SELECT p.cardnumber, p.surname, p.firstname, DATE_FORMAT(c.issuedate, "%m/%d/%Y") AS Issue_Date, DATE_FORMAT(c.date_due, "%m/%d/%Y") AS Due_Date, h.attribute AS teacher, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', b.title,

Re: [Koha] SQL Report help

Jeremy, You can join biblioitems to biblio using biblionumber. Add this right before your WHERE clause: LEFT JOIN biblioitems bi ON (b.biblionumber = bi.biblionumber) Once you've done that, you can add b.biblionumber, ExtractValue(bi.marcxml,'//datafield[@tag="020"]/subfield[@code="c"]') AS hea

Re: [Koha] SQL report: Digests in message preferences

(General reminder to add things like this to the wiki) Cheers, Liz :) On 02/04/15 05:37, Chad Roseburg wrote: > Thanks Jonathan! Exactly what I was looking for. > > On Wed, Apr 1, 2015 at 1:31 AM, Jonathan Druart < > jonathan.dru...@biblibre.com> wrote: > >> Hello Chad, >> >> It's stored in the b

Re: [Koha] SQL report: Digests in message preferences

Thanks Jonathan! Exactly what I was looking for. On Wed, Apr 1, 2015 at 1:31 AM, Jonathan Druart < jonathan.dru...@biblibre.com> wrote: > Hello Chad, > > It's stored in the borrower_message_preferences.wants_digest column. > So the following query should return what you want: > > select distinct

Re: [Koha] SQL report: Digests in message preferences

Hello Chad, It's stored in the borrower_message_preferences.wants_digest column. So the following query should return what you want: > select distinct borrowernumber from borrower_message_preferences where > wants_digest=1; Regards, Jonathan 2015-04-01 1:11 GMT+02:00 Chad Roseburg : > Does anyo

Re: [Koha] sql report for bibliographic records with more than one subject

So I researched on this for the past few days, scouring the internet, only to find partly the answer in mysql-xml documentation (http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html), I came up with the following: SELECT ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') AS T

Re: [Koha] SQL Report help please

Hi Ann, Have you checked the ready-made reports in KOHA Wiki (SQL Reports Library)? http://wiki.koha-community.org/wiki/SQL_Reports_Library Regards, On Wed, Sep 3, 2014 at 9:06 AM, library wrote: > Ata marie > > Could someone help please. I am trying to produce reading statistics based > on

Re: [Koha] SQL report 650 a

We are using following sql report, change your barcode range and try SELECT items.barcode, biblio.author, CONCAT(biblio.title, ", ",IF( LOCATE('', biblioitems.marcxml, LOCATE('', biblioitems.marcxml, LOCATE(' LOCATE('', biblioitems.marcxml, LOCATE('', biblioitems.marcxml, LOCATE('', biblioitems.ma

Re: [Koha] SQL report 650 a

On 03/01/14 23:31, Holger Meissner wrote: yoonus para wrote: Dear all, How to create a SQL report using field 650 a with barcode and title,Please help me to create this report. Thanks Yoonus Did you find a solution? I thought it might be interesting, but I also can't figure it out. When I

Re: [Koha] SQL report 650 a

yoonus para wrote: > Dear all, > > How to create a SQL report using field 650 a with barcode and title,Please > help me to create this report. > > Thanks > Yoonus Did you find a solution? I thought it might be interesting, but I also can't figure it out. When I run SELECT * FROM marc_subfield_s

Re: [Koha] Sql report for list of items according to shelving location

Hi Godfrey, you do not specify fields you are interested in. Only some fields from the items table? Or some fields from the bilio table too? Anyway, maybe you can start trying select * from items order by cn_sort or select * from biblio b, items i where b.biblionumber=i.biblionumber order by i.

Re: [Koha] Sql report for list of items according to shelving location

Halo friends, I need technical help in sql, Am at zero level hence I need your help from scratch. Always grateful. Godfrey Quoting Saira Asghar : Hello Everyone I need a report for list of items according to shelving location. If anyone has this kind of sql report then please send me or he

Re: [Koha] Sql report for list of items according to shelving location

Dear Mr. Paul. A Thank you so much for sharing this report. This is absolutely according to my need. Thanks again On Fri, Nov 1, 2013 at 8:28 PM, Paul A wrote: > At 06:54 PM 11/1/2013 +0500, Saira Asghar wrote: > >> Hello Everyone >> >> I need a report for list of items according to shelving l

Re: [Koha] Sql report for list of items according to shelving location

At 06:54 PM 11/1/2013 +0500, Saira Asghar wrote: Hello Everyone I need a report for list of items according to shelving location. If anyone has this kind of sql report then please send me or help me in generating this. You might try: SELECT biblio.author,biblio.title,biblio.copyrightdate,item

Re: [Koha] sql report 952 z (non-public note)

: Re: [Koha] sql report 952 z (non-public note) Chris, We use the non-public note to hold our information about the actual physical floor/row/bay/shelf information of the boxes in our archives (so we call it "location" in our query) and to print out call slips. We use a sql state

Re: [Koha] sql report 952 z (non-public note)

On 13 September 2011 01:20, Linda Culberson wrote: > Chris, > We use the non-public note to hold our information about the actual physical > floor/row/bay/shelf information of the boxes in our archives (so we call it > "location" in our query)  and to print out call slips. We use a sql > statement

Re: [Koha] sql report 952 z (non-public note)

Chris, We use the non-public note to hold our information about the actual physical floor/row/bay/shelf information of the boxes in our archives (so we call it "location" in our query) and to print out call slips. We use a sql statement that includes; ExtractValue(more_subfields_xml, '/colle