You should share this on the Koha wiki: http://wiki.koha-community.org/wiki/SQL_Reports_Library Nicole 2011/3/16 Nikunj Tyagi <nikunj1@gmail.com>:

Hi, I am using following sql for  serial received during the particular period (month), you can create same for missing serials after changing the sql code in subscriptionhistory    SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate, IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19, LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19))) AS FREQUENCY  FROM serial, biblio,biblioitems WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND  MONTH(planneddate) = 03 AND YEAR(planneddate)= 2011 ORDER BY serial.subscriptionid ASC

Nikunj Tyagi Delhi Public Library

On Wed, Mar 16, 2011 at 1:05 AM, <koha-request@lists.katipo.co.nz> wrote:

...

Send Koha mailing list submissions to        koha@lists.katipo.co.nz

To subscribe or unsubscribe via the World Wide Web, visit        http://lists.katipo.co.nz/mailman/listinfo/koha or, via email, send a message with subject or body 'help' to        koha-request@lists.katipo.co.nz

You can reach the person managing the list at        koha-owner@lists.katipo.co.nz

When replying, please edit your Subject line so it is more specific than "Re: Contents of Koha digest..."

Today's Topics:

  1. Re: Creating a report for serials (Peel Lisa)   2. Re: Creating a report for serials (Nicole Engard)

----------------------------------------------------------------------

Message: 1 Date: Tue, 15 Mar 2011 19:21:48 +0000 From: Peel Lisa <lisa.peel@ritz.edu> Subject: Re: [Koha] Creating a report for serials To: 'Nicole Engard' <nengard@gmail.com> Cc: "'koha@lists.katipo.co.nz'" <koha@lists.katipo.co.nz> Message-ID:

 <4501112D012181428D5AB62DDE501768022BDD@IHCRSV025.admin.ihcr.ritz.local>

Content-Type: text/plain; charset="utf-8"

Hi Nicole

Still on 3.00 I am afraid !

Lisa

From: Nicole Engard [mailto:nengard@gmail.com] Sent: 15 March 2011 20:11 To: Peel Lisa Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Creating a report for serials

Lisa,

My list of claims is showing me the branch code. What version of Koha are you using?

Nicole 2011/3/15 Peel Lisa <lisa.peel@ritz.edu<mailto:lisa.peel@ritz.edu>> Hello all

We have had our serials  module live for about 6 months now so I thought it was about time I looked at the Claims ? however, it does not tell me which library each subscription is for so it is difficult for me to know which missed/late serials to direct to which library.

Could someone perhaps provide me with the SQL to run a report which will give me the information which Claims gives me but also with the added information of Library (Claims currently gives me Supplier, Title, Issue Number, Status, and Since). I normally try and tweak one of the reports in the SQL Reports Library but I cannot find any for the Serials module.

Thanks in advance.

Regards

Lisa

[Description: Description: Description: CR_logo_POS_COULEUR b]

LISA PEEL, PGDip, MA. Librarian C?sar Ritz Colleges Switzerland 1897 Le Bouveret - Switzerland Tel: +41 24 482 82 82<tel:%2B41%2024%20482%2082%2082> Fax: +41 24 482 82 80<tel:%2B41%2024%20482%2082%2080> E-mail: lisa.peel@ritz.edu<mailto:hortense.hofer@ritz.edu> http://www.ritz.edu<http://www.ritz.edu/> - http://www.culinaryarts.ch<http://www.culinaryarts.ch/>

_______________________________________________ Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> http://lists.katipo.co.nz/mailman/listinfo/koha