[Koha] Koha Digest, Vol 65, Issue 61

Thu Mar 17 01:20:08 NZDT 2011

You should share this on the Koha wiki:
http://wiki.koha-community.org/wiki/SQL_Reports_Library

Nicole

2011/3/16 Nikunj Tyagi <nikunj1 at 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 at lists.katipo.co.nz> wrote:
>>
>> Send Koha mailing list submissions to
>>        koha at 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 at lists.katipo.co.nz
>>
>> You can reach the person managing the list at
>>        koha-owner at 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 at ritz.edu>
>> Subject: Re: [Koha] Creating a report for serials
>> To: 'Nicole Engard' <nengard at gmail.com>
>> Cc: "'koha at lists.katipo.co.nz'" <koha at lists.katipo.co.nz>
>> Message-ID:
>>
>>  <4501112D012181428D5AB62DDE501768022BDD at 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 at gmail.com]
>> Sent: 15 March 2011 20:11
>> To: Peel Lisa
>> Cc: koha at 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 at ritz.edu<mailto:lisa.peel at 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 at ritz.edu<mailto:hortense.hofer at 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 at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL:
>> http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/df8e55bf/attachment-0001.htm
>> -------------- next part --------------
>> A non-text attachment was scrubbed...
>> Name: image001.jpg
>> Type: image/jpeg
>> Size: 4030 bytes
>> Desc: image001.jpg
>> Url :
>> http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/df8e55bf/attachment-0001.jpg
>>
>> ------------------------------
>>
>> Message: 2
>> Date: Tue, 15 Mar 2011 15:34:52 -0400
>> From: Nicole Engard <nengard at gmail.com>
>> Subject: Re: [Koha] Creating a report for serials
>> To: Peel Lisa <lisa.peel at ritz.edu>
>> Cc: "koha at lists.katipo.co.nz" <koha at lists.katipo.co.nz>
>> Message-ID:
>>        <AANLkTin9mnKukur7ToJUBcoWqruHfW8e+t_WiucJec-X at mail.gmail.com>
>> Content-Type: text/plain; charset="utf-8"
>>
>> The latest release is 3.2.5 and 3.2.6 is due in less than 10 days, you
>> might
>> want to consider upgrading :)
>>
>> Nicole
>>
>> On Tue, Mar 15, 2011 at 3:21 PM, Peel Lisa <lisa.peel at ritz.edu> wrote:
>>
>> >  Hi Nicole
>> >
>> >
>> >
>> > Still on 3.00 I am afraid !
>> >
>> >
>> >
>> > Lisa
>> >
>> >
>> >
>> > *From:* Nicole Engard [mailto:nengard at gmail.com]
>> > *Sent:* 15 March 2011 20:11
>> > *To:* Peel Lisa
>> > *Cc:* koha at 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 at 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
>> >
>> >
>> >
>> > [image: Description: Description: Description: CR_logo_POS_COULEUR b]
>> >
>> > * *
>> >
>> > * *
>> >
>> > *LISA PEEL, PGDip, MA.*
>> >
>> > Librarian
>> >
>> > C?sar Ritz Colleges Switzerland
>> >
>> > 1897 Le Bouveret - Switzerland
>> >
>> > Tel:  <%2B41%2024%20482%2082%2082>+41 24 482 82 82 Fax:
>> > <%2B41%2024%20482%2082%2080>+41
>> > 24 482 82 80
>> >
>> > E-mail: *lisa.peel at ritz.edu <hortense.hofer at ritz.edu>*
>> >
>> > http://www.ritz.edu - http://www.culinaryarts.ch
>> >
>> >
>> >
>> >
>> > _______________________________________________
>> > Koha mailing list  http://koha-community.org
>> > Koha at lists.katipo.co.nz
>> > http://lists.katipo.co.nz/mailman/listinfo/koha
>> >
>> >
>> >
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL:
>> http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/76fa3a3a/attachment.htm
>> -------------- next part --------------
>> A non-text attachment was scrubbed...
>> Name: not available
>> Type: image/jpeg
>> Size: 4030 bytes
>> Desc: not available
>> Url :
>> http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/76fa3a3a/attachment.jpeg
>>
>> ------------------------------
>>
>> _______________________________________________
>> Koha mailing list
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>>
>> End of Koha Digest, Vol 65, Issue 61
>> ************************************
>
>
>
> --
> Thanks & Regards,
> Nikunj Tyagi
>
>  “Clean out a corner of your mind and creativity will instantly fill it.”
>
> - Dee Hock
>
>  “अपने दिल दिमाग के थोड़े से भी हिस्से से आप बुराईयों को निकाल बाहर कीजिए,
> तुरंत उस रिक्त स्थान को सृजनात्मकता भर देगी.”
>
> - डी हॉक
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>