Serial reports

From Koha Wiki
Jump to navigation Jump to search

This is the page that relates to serials reports.

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Shows the total serial received during the month

  • Developer: Nikunj Tyagi, DPL
  • Module: Serial
  • Purpose: Shows the total serials received with Title, Frequency, latest issue detail
  • Status: Complete
  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 AND (status)=2
ORDER BY serial.subscriptionid ASC

missing/late/claimed serial during the month

  • Developer: Nikunj Tyagi, DPL
  • Module: Serial
  • Purpose: Shows the total serials missing/late/claimed with Title, Frequency, latest issue detail status 3=late,4=missing,5=claimed
  • Status: Complete
SELECT
  s.subscriptionid,
  s.biblionumber,
  s.serialid,
  b.title,
  s.serialseq,
  s.planneddate,
  s.publisheddate,
  IF(LOCATE('<datafield tag="310"', bm.metadata) = 0 
    OR LOCATE('<subfield code="a">', bm.metadata,
      LOCATE('<datafield tag="310"', bm.metadata)) = 0 
    OR LOCATE('<subfield code="a">', bm.metadata,
      LOCATE('<datafield tag="310"', bm.metadata)) >
        LOCATE('</datafield>', bm.metadata,
          LOCATE('<datafield tag="310"', bm.metadata)),
    '',
    SUBSTRING( bm.metadata,
      LOCATE('<subfield code="a">', bm.metadata,
        LOCATE('<datafield tag="310"', bm.metadata)) + 19,
      LOCATE('</subfield>', bm.metadata,
        LOCATE('<subfield code="a">', bm.metadata,
          LOCATE('<datafield tag="310"', bm.metadata)) + 19) -
      (LOCATE('<subfield code="a">', bm.metadata,
        LOCATE('<datafield tag="310"', bm.metadata)) + 19))
  ) AS FREQUENCY,
  s.status 
FROM
  serial s,
  biblio b,
  biblioitems bi,
  biblio_metadata bm
WHERE
  s.biblionumber = b.biblionumber AND
  s.biblionumber = bi.biblionumber AND
  bm.biblionumber = b.biblionumber AND 
  MONTH(planneddate) =  10  AND
  YEAR(planneddate) = 2023 AND
  (status) BETWEEN '3' AND '5' 
ORDER BY
  s.subscriptionid ASC

Late Issues

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Serials
  • Purpose: A list of items that should have arrived by now
  • Status: Complete
select b.title, s.serialseq, s.planneddate 
from serial s 
left join biblio b using (biblionumber) 
where s.planneddate < CURDATE() and s.status not in ('1','2')

Latest Issues

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Serials
  • Purpose: A list of the latest issue received for each subscription
  • Status: Complete
select b.title, b.biblionumber, MAX(CONCAT(s.publisheddate, ' / ',s.serialseq)) as 'date and enumeration' 
from serial s 
left join biblio b using (biblionumber) 
where s.status=2 
group by b.biblionumber 
order by s.publisheddate desc

Issues Received in a Range

  • Developer: Nicole C. Baratta, ByWater Solutions with MJ Ray
  • Module: Serials
  • Purpose: A list of your serials and the year range you have received
  • Status: Complete
SELECT b.title, i.issn, 
      CONCAT(YEAR(MIN(s.publisheddate)), ' to ', YEAR(MAX(s.publisheddate))) AS 'range' 
FROM serial s
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN biblioitems i USING (biblionumber)
WHERE s.STATUS=2
GROUP BY b.biblionumber
ORDER BY b.title ASC

Year range of serials holdings

  • Developer: Paul Landers
  • Module: Serials
  • Purpose: Year range of all serial subscription titles by holding branch
  • Status: Complete
SELECT 
    s.biblionumber,
    c.issn AS ISSN,
    i.holdingbranch,
    b.title AS Title,
    ExtractValue(m.metadata,'//datafield[@tag="222"]/subfield[@code>="a"]') AS 'MARC 222 Title',
    YEAR(MIN(s.publisheddate)) AS 'begin',
    YEAR(MAX(s.publisheddate)) AS 'end' 
    
FROM serial s,items i,biblio b, biblioitems c, biblio_metadata m, serialitems si
WHERE si.itemnumber=i.itemnumber  
AND i.biblionumber=b.biblionumber 
AND b.biblionumber=c.biblionumber 
AND i.biblionumber=m.biblionumber
AND si.serialid = s.serialid
AND i.holdingbranch = <<Branch|branches>>
GROUP BY b.biblionumber
ORDER BY b.title

Fix Subscriptions without Subscription Start Dates

  • Developer: Barton Chittenden
  • Module: Serials
  • Purpose: Fix serials that generate the following error message: Software error: Date::Calc::Add_Delta_YM(): not a valid date at /var/lib/koha/aarome/kohaclone/C4/Serials.pm line 2651.
  • Status: Complete
select 
    CONCAT(
        '<a href="http://',
        (SELECT value 
         FROM systempreferences 
         WHERE variable='staffClientBaseURL'),
        '/cgi-bin/koha/serials/subscription-history.pl?subscriptionid=',
        subscriptionid,
        '">', title, '</a>'
    ) as 'Edit Subscription for',
    subscriptionid,
    startdate,
    histstartdate
from
    subscription
    inner join biblio using (biblionumber)
    left join subscriptionhistory using (subscriptionid) 
    left join serial using (subscriptionid) 
where
    startdate is NULL
    or histstartdate is NULL
group by subscriptionid

List of Current Periodicals and their holdings

  • Developer: Joseph Alway
  • Module: Serials
  • Purpose: List current subscriptions.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
    biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
    subscription
    JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )
    JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )
    LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )
WHERE
    (subscription.closed = 0)
GROUP BY biblio.biblionumber
ORDER BY biblio.title

List of Non Current Periodicals and their holdings

  • Developer: Joseph Alway
  • Module: Serials
  • Purpose: List closed subscriptions.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
    biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN, GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS Holdings
FROM
    subscription
    JOIN biblio ON ( subscription.biblionumber = biblio.biblionumber )
    JOIN biblio_metadata ON ( subscription.biblionumber = biblio_metadata.biblionumber )
    LEFT JOIN serial ON ( subscription.subscriptionid = serial.subscriptionid )
WHERE
    (subscription.closed != 0)
GROUP BY biblio.biblionumber
ORDER BY biblio.title

List of Non Current Periodicals and their holdings *Special Case*

  • Developer: Joseph Alway
  • Module: Serials
  • Purpose: List Holdings information from Marc Record and closed subscriptions. Combines two reports into one.
  • Status: Complete
SELECT 
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    NULL AS subscriptionid, biblio.title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN,
    ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) as Holdings, NULL AS 'S. Holdings'
FROM 
    biblio_metadata
    JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE
    (ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]' ) LIKE "CR")
    AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "%See%Subscriptions%for%More%Details%")
    AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "%Current%issues%")
    AND (ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) NOT LIKE "")
UNION ALL
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber,
    CONCAT('<a href=\"/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',subscription.subscriptionid,'\">',subscription.subscriptionid,'</a>') AS subscriptionid,
    biblio.title as title, ExtractValue( metadata, '//datafield[@tag="022"]/subfield[@code="a"]' ) as ISSN, ExtractValue( metadata, '//datafield[@tag="966"]/subfield[@code="a"]' ) as Holdings,
    GROUP_CONCAT(serial.serialseq SEPARATOR '; ') AS 'S. Holdings'
FROM
    subscription
    JOIN biblio ON (subscription.biblionumber = biblio.biblionumber)
    JOIN biblio_metadata ON (subscription.biblionumber = biblio_metadata.biblionumber)
    LEFT JOIN serial ON (subscription.subscriptionid = serial.subscriptionid)
WHERE
    (subscription.closed != 0)
GROUP BY biblio.biblionumber
ORDER BY title
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Serial_reports&oldid=38529"