Talk:SQL Reports Library

To Eliminate duplicate orders

• Module: Acquisitions
• Summary: I want to write an SQL Query to eliminate duplicate orders from a selected vendor. Please help me in this query.

To generate report on Check-in and Check-out by Library Staff

• Module: Circulation
• Summary: I want to generate a report about check-in and check-out of an item by which Library Staff or by Staff ID i.e. which library staff circulated the item to user. Is it possible? If possible then How? What is the SQL Query for this purpose? Please help me in this query.

Separate this page into multiple pages?

• This is becoming a great resource, which is wonderful. However, even with the table of contents, it's daunting. I think we might want to consider separating things out and giving each category its own page. Thoughts? Brooke 05:17, 22 January 2014 (EST)

Next stage of SQL Report Library

• We need much more than just splitting this article in chunks of reports. We should organize as what they really are: records. One possibility is using some library software to just handle them (not just save/show, but why not also vote/tag/export) what community provide (their plugins, scripts, SQL Reports, etc). In the meantime, I'm searching for a way to change this article so it looks similar but add the possibility (using MediaWiki API) to for example made a script to "easily" fetch a report as a record.--Pabloab 12:45, 20 October 2014 (EDT)

Alternatives (feel free to add):

• Semantic MediaWiki Extension
• Use a MediaWiki Template

SQL Reports Library structured in templates

In the same line of what I wrote before I created a MediaWiki Report Template and this article with some examples. In the future this could replace the current SQL Reports Library with this -more structured- alternative.

add a new report - OPAC usage statistics

Hello,

I want to add a report to the library, but do not really know how and where. I recently created a report for OPAC Usage statistics, because I did not find one here already. Would I need to add it under 8.10 or 13.8?

SELECT search_history.sessionid,
       search_history.query_desc,
       search_history.type,
       search_history.total,
       search_history.time,
       borrowers.branchcode,
       borrowers.categorycode
FROM search_history
JOIN borrowers ON (search_history.userid=borrowers.borrowernumber)
JOIN branches ON (borrowers.branchcode=branches.branchcode)
WHERE borrowers.branchcode=<<
  SELECT Library|branches>>
  AND search_history.time BETWEEN <<SEARCH history TIMESTAMP BETWEEN (yyyy-mm-dd)|date>> AND <<AND (yyyy-mm-dd)|date>>

And I noticed a small problem with the second date, it is not included, so one needs to add a day when running the report.

Thanks in advance for any reply. --Steffi

Probably bug on the example of "Runtime Variables" section

I edit the example on SQL Reports Library#Runtime_Variables, now that we use the biblio_metadata table. But a problem arises:

"Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'like'".

This might be related to Bug 18686. ----Pabloab 19:35, 1 November 2020 (EST)

Add a new report for circulation

 I have written a report but doesn't quite bring the right results:

SELECT DISTINCT items.onloan, items.barcode, b.cardnumber, b.surname, b.firstname, biblio.title
FROM borrowers b
LEFT JOIN statistics ON (statistics.borrowernumber=b.borrowernumber)
LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
LEFT JOIN biblio ON (biblio.biblionumber = items.biblionumber)
WHERE homebranch = <<Pick your branch|branches>>
ORDER BY items.onloan desc, b.surname asc, b.firstname asc

For some reason, there seems to be some mistake in the code, but I don't know what. All I was trying to do is create a list of borrowed items even if not overdue. What I get in return, is a very weird list where the same item seems to be simultaneously borrowed to 2 or more different borrowers.

Any suggestions to improve the code? Thank you!

"Biblios with like data in a subfield of a field" got deleted

In the contents of the page  3.4.1.4 is empty. I believe there used to be a report under the title "Biblios with like data in a subfield of a field". I don't remember the Developer's name, but the code was 

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblio_metadata
JOIN biblio USING (biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

I have also made an addition in the select part so that it shows the value of the variable searched in where clause, in case that % has been used to search all values of the field.

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author, ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AS 'Variable'
FROM biblio_metadata
JOIN biblio USING (biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

I'm not very familiar with how to edit the SQL Report Library page and, as I've already messed up in the past, I used the talk page to ask for help, so thanks in advance!

"Biblios with like data in a subfield of a field" got deleted

In the contents of the page  3.4.1.4 is empty. I believe there used to be a report under the title "Biblios with like data in a subfield of a field". I don't remember the Developer's name, but the code was 

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author
FROM biblio_metadata
JOIN biblio USING (biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

I have also made an addition in the select part so that it shows the value of the variable searched in where clause, in case that % has been used to search all values of the field.

SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title, biblio.author, ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]") AS 'Variable'
FROM biblio_metadata
JOIN biblio USING (biblionumber)
WHERE ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
LIKE <<Search Term (USE % AS wildcard)>>

I'm not very familiar with how to edit the SQL Report Library page and, as I've already messed up in the past, I used the talk page to ask for help, so thanks in advance!