Hi,<br>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<br> SELECT serial.subscriptionid,serial.biblionumber,serial.serialid,biblio.title,serial.serialseq,serial.planneddate,serial.publisheddate, <br>
IF( LOCATE('<datafield tag="310"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, <br>LOCATE('<datafield tag="310"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="a">', biblioitems.marcxml, <br>
LOCATE('<datafield tag="310"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)), '', <br>
SUBSTRING( biblioitems.marcxml,<br>LOCATE('<subfield code="a">', biblioitems.marcxml, LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19, <br>LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="a">', biblioitems.marcxml, <br>
LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19) -(LOCATE('<subfield code="a">', biblioitems.marcxml, <br>LOCATE('<datafield tag="310"', biblioitems.marcxml)) + 19))) <br>
AS FREQUENCY FROM serial, biblio,biblioitems<br>WHERE serial.biblionumber = biblio.biblionumber AND serial.biblionumber=biblioitems.biblionumber AND MONTH(planneddate) = 03 AND YEAR(planneddate)= 2011<br>ORDER BY serial.subscriptionid ASC<br>
<br>Nikunj Tyagi<br>Delhi Public Library<br><br><div class="gmail_quote">On Wed, Mar 16, 2011 at 1:05 AM, <span dir="ltr"><<a href="mailto:koha-request@lists.katipo.co.nz" target="_blank">koha-request@lists.katipo.co.nz</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Send Koha mailing list submissions to<br>
<a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a><br>
<br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
or, via email, send a message with subject or body 'help' to<br>
<a href="mailto:koha-request@lists.katipo.co.nz" target="_blank">koha-request@lists.katipo.co.nz</a><br>
<br>
You can reach the person managing the list at<br>
<a href="mailto:koha-owner@lists.katipo.co.nz" target="_blank">koha-owner@lists.katipo.co.nz</a><br>
<br>
When replying, please edit your Subject line so it is more specific<br>
than "Re: Contents of Koha digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. Re: Creating a report for serials (Peel Lisa)<br>
2. Re: Creating a report for serials (Nicole Engard)<br>
<br>
<br>
----------------------------------------------------------------------<br>
<br>
Message: 1<br>
Date: Tue, 15 Mar 2011 19:21:48 +0000<br>
From: Peel Lisa <<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a>><br>
Subject: Re: [Koha] Creating a report for serials<br>
To: 'Nicole Engard' <<a href="mailto:nengard@gmail.com" target="_blank">nengard@gmail.com</a>><br>
Cc: "'<a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a>'" <<a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a>><br>
Message-ID:<br>
<4501112D012181428D5AB62DDE501768022BDD@IHCRSV025.admin.ihcr.ritz.local><br>
<br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Hi Nicole<br>
<br>
Still on 3.00 I am afraid !<br>
<br>
Lisa<br>
<br>
From: Nicole Engard [mailto:<a href="mailto:nengard@gmail.com" target="_blank">nengard@gmail.com</a>]<br>
Sent: 15 March 2011 20:11<br>
To: Peel Lisa<br>
Cc: <a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a><br>
Subject: Re: [Koha] Creating a report for serials<br>
<br>
Lisa,<br>
<br>
My list of claims is showing me the branch code. What version of Koha are you using?<br>
<br>
Nicole<br>
2011/3/15 Peel Lisa <<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a><mailto:<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a>>><br>
Hello all<br>
<br>
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.<br>
<br>
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.<br>
<br>
Thanks in advance.<br>
<br>
Regards<br>
<br>
Lisa<br>
<br>
[Description: Description: Description: CR_logo_POS_COULEUR b]<br>
<br>
<br>
LISA PEEL, PGDip, MA.<br>
Librarian<br>
C?sar Ritz Colleges Switzerland<br>
1897 Le Bouveret - Switzerland<br>
Tel: +41 24 482 82 82<tel:%2B41%2024%20482%2082%2082> Fax: +41 24 482 82 80<tel:%2B41%2024%20482%2082%2080><br>
E-mail: <a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a><mailto:<a href="mailto:hortense.hofer@ritz.edu" target="_blank">hortense.hofer@ritz.edu</a>><br>
<a href="http://www.ritz.edu" target="_blank">http://www.ritz.edu</a><<a href="http://www.ritz.edu/" target="_blank">http://www.ritz.edu/</a>> - <a href="http://www.culinaryarts.ch" target="_blank">http://www.culinaryarts.ch</a><<a href="http://www.culinaryarts.ch/" target="_blank">http://www.culinaryarts.ch/</a>><br>
<br>
<br>
_______________________________________________<br>
Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><mailto:<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a>><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
<br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <a href="http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/df8e55bf/attachment-0001.htm" target="_blank">http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/df8e55bf/attachment-0001.htm</a><br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: image001.jpg<br>
Type: image/jpeg<br>
Size: 4030 bytes<br>
Desc: image001.jpg<br>
Url : <a href="http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/df8e55bf/attachment-0001.jpg" target="_blank">http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/df8e55bf/attachment-0001.jpg</a><br>
<br>
------------------------------<br>
<br>
Message: 2<br>
Date: Tue, 15 Mar 2011 15:34:52 -0400<br>
From: Nicole Engard <<a href="mailto:nengard@gmail.com" target="_blank">nengard@gmail.com</a>><br>
Subject: Re: [Koha] Creating a report for serials<br>
To: Peel Lisa <<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a>><br>
Cc: "<a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a>" <<a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a>><br>
Message-ID:<br>
<<a href="mailto:AANLkTin9mnKukur7ToJUBcoWqruHfW8e%2Bt_WiucJec-X@mail.gmail.com" target="_blank">AANLkTin9mnKukur7ToJUBcoWqruHfW8e+t_WiucJec-X@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
The latest release is 3.2.5 and 3.2.6 is due in less than 10 days, you might<br>
want to consider upgrading :)<br>
<br>
Nicole<br>
<br>
On Tue, Mar 15, 2011 at 3:21 PM, Peel Lisa <<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a>> wrote:<br>
<br>
> Hi Nicole<br>
><br>
><br>
><br>
> Still on 3.00 I am afraid !<br>
><br>
><br>
><br>
> Lisa<br>
><br>
><br>
><br>
> *From:* Nicole Engard [mailto:<a href="mailto:nengard@gmail.com" target="_blank">nengard@gmail.com</a>]<br>
> *Sent:* 15 March 2011 20:11<br>
> *To:* Peel Lisa<br>
> *Cc:* <a href="mailto:koha@lists.katipo.co.nz" target="_blank">koha@lists.katipo.co.nz</a><br>
> *Subject:* Re: [Koha] Creating a report for serials<br>
><br>
><br>
><br>
> Lisa,<br>
><br>
> My list of claims is showing me the branch code. What version of Koha are<br>
> you using?<br>
><br>
> Nicole<br>
><br>
> 2011/3/15 Peel Lisa <<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a>><br>
><br>
> Hello all<br>
><br>
><br>
><br>
> We have had our serials module live for about 6 months now so I thought it<br>
> was about time I looked at the Claims ? however, it does not tell me which<br>
> library each subscription is for so it is difficult for me to know which<br>
> missed/late serials to direct to which library.<br>
><br>
><br>
><br>
> Could someone perhaps provide me with the SQL to run a report which will<br>
> give me the information which Claims gives me but also with the added<br>
> information of Library (Claims currently gives me Supplier, Title, Issue<br>
> Number, Status, and Since). I normally try and tweak one of the reports in<br>
> the SQL Reports Library but I cannot find any for the Serials module.<br>
><br>
><br>
><br>
> Thanks in advance.<br>
><br>
><br>
><br>
> Regards<br>
><br>
><br>
><br>
> Lisa<br>
><br>
><br>
><br>
> [image: Description: Description: Description: CR_logo_POS_COULEUR b]<br>
><br>
> * *<br>
><br>
> * *<br>
><br>
> *LISA PEEL, PGDip, MA.*<br>
><br>
> Librarian<br>
><br>
> C?sar Ritz Colleges Switzerland<br>
><br>
> 1897 Le Bouveret - Switzerland<br>
><br>
> Tel: <%2B41%2024%20482%2082%2082>+41 24 482 82 82 Fax: <%2B41%2024%20482%2082%2080>+41<br>
> 24 482 82 80<br>
><br>
> E-mail: *<a href="mailto:lisa.peel@ritz.edu" target="_blank">lisa.peel@ritz.edu</a> <<a href="mailto:hortense.hofer@ritz.edu" target="_blank">hortense.hofer@ritz.edu</a>>*<br>
><br>
> <a href="http://www.ritz.edu" target="_blank">http://www.ritz.edu</a> - <a href="http://www.culinaryarts.ch" target="_blank">http://www.culinaryarts.ch</a><br>
><br>
><br>
><br>
><br>
> _______________________________________________<br>
> Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
> <a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><br>
> <a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
><br>
><br>
><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <a href="http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/76fa3a3a/attachment.htm" target="_blank">http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/76fa3a3a/attachment.htm</a><br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: not available<br>
Type: image/jpeg<br>
Size: 4030 bytes<br>
Desc: not available<br>
Url : <a href="http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/76fa3a3a/attachment.jpeg" target="_blank">http://lists.katipo.co.nz/pipermail/koha/attachments/20110315/76fa3a3a/attachment.jpeg</a><br>
<br>
------------------------------<br>
<br>
_______________________________________________<br>
Koha mailing list<br>
<a href="mailto:Koha@lists.katipo.co.nz" target="_blank">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
<br>
<br>
End of Koha Digest, Vol 65, Issue 61<br>
************************************<br>
</blockquote></div><br><br clear="all"><br>-- <br>Thanks & Regards, <br>Nikunj Tyagi<br><br><p><span> </span>“Clean out a corner
of your mind and creativity will instantly fill it.” </p>
<p>- Dee Hock</p>
<p> “<span style="font-family: "Mangal","serif";">अपने</span> <span style="font-family: "Mangal","serif";">दिल</span> <span style="font-family: "Mangal","serif";">दिमाग</span>
<span style="font-family: "Mangal","serif";">के</span> <span style="font-family: "Mangal","serif";">थोड़े</span> <span style="font-family: "Mangal","serif";">से</span>
<span style="font-family: "Mangal","serif";">भी</span> <span style="font-family: "Mangal","serif";">हिस्से</span> <span style="font-family: "Mangal","serif";">से</span>
<span style="font-family: "Mangal","serif";">आप</span> <span style="font-family: "Mangal","serif";">बुराईयों</span> <span style="font-family: "Mangal","serif";">को</span>
<span style="font-family: "Mangal","serif";">निकाल</span> <span style="font-family: "Mangal","serif";">बाहर</span> <span style="font-family: "Mangal","serif";">कीजिए</span>,
<span style="font-family: "Mangal","serif";">तुरंत</span> <span style="font-family: "Mangal","serif";">उस</span> <span style="font-family: "Mangal","serif";">रिक्त</span>
<span style="font-family: "Mangal","serif";">स्थान</span> <span style="font-family: "Mangal","serif";">को</span> <span style="font-family: "Mangal","serif";">सृजनात्मकता</span>
<span style="font-family: "Mangal","serif";">भर</span> <span style="font-family: "Mangal","serif";">देगी</span>.”</p><p></p>
<p>- <span style="font-family: "Mangal","serif";">डी</span> <span style="font-family: "Mangal","serif";">हॉक</span></p><br>