Course Reserves Reports

This is the page that relates to course reserves 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

All course reserve items

• Developer: Nicole C. Baratta, ByWater Solutions
• Module: Course Reserves
• Purpose: All titles on reserve

select b.title, i.itype, t.itype as 'course item type', i.ccode, t.ccode as 'course ccode', 
i.itemcallnumber, i.barcode, i.itemnotes, c.course_name 
from course_items t 
left join  items i using (itemnumber) 
left join biblio b using (biblionumber) 
left join course_reserves r using (ci_id) 
left join courses c using (course_id)

Course reserve items with circulation counts by term

• Developer: Joe Sikowitz, Fenway Library Organizatoin
• Module: Course Reserves
• Purpose: To show items on course reserve during a given term and how often they have circulated.
• Status: Completed
• Works with: 18.05

SELECT
  i.barcode AS 'Barcode',
  b.title AS 'Title',
  i.itemcallnumber AS 'Call #',
  i.itype AS 'Item Type',
  c.term AS 'Term',
  c.department AS 'Department',
  c.course_name AS 'Course Name',
  c.course_number AS 'Course Number',
  bo.surname AS 'Instructor',
  COUNT(st.datetime) AS 'Circ Total'
FROM course_items ci
  LEFT JOIN (
    SELECT
      s.itemnumber,
      s.datetime
    FROM statistics s
    WHERE
      s.datetime BETWEEN <<Circ start date|date>> AND
      <<Circ end date|date>> AND s.type='issue') AS st ON (st.itemnumber = ci.itemnumber)
  LEFT JOIN items i ON (i.itemnumber = ci.itemnumber)
  LEFT JOIN biblio b ON (i.biblionumber = b.biblionumber)
  LEFT JOIN course_reserves cr ON (cr.ci_id = ci.ci_id)
  LEFT JOIN courses c ON (c.course_id = cr.course_id)
  LEFT JOIN course_instructors ins ON ( ins.course_id = c.course_id)
  LEFT JOIN borrowers bo ON (bo.borrowernumber = ins.borrowernumber)
WHERE
  i.homebranch LIKE <<Select school|library_group>> AND c.term LIKE <<Select term|term>>
GROUP BY
  ci.itemnumber
ORDER BY
  c.department,
  c.term,
  bo.surname,
  c.course_number