[OPEN-ILS-GENERAL] Call Number/Checkout report

Thu Dec 4 13:33:19 EST 2014

Assuming your data data is good you may actually want to check for two
different things, items that haven't circulated in ten + years and items
that have never circulated.   I also recommend checking to see the age of
the item and if it was added 10 + years ago.  Again, this may or may not be
practical depending on your migrated data.

This is the report I use.  It could be streamlined a bit, especially if
you're not concerned about the branch or creation date but it does check
for the duration since last used (or never), currently set to three years
because I mostly use it for popular fiction.  And it checks a shelving
location.

The child shortname is our branches because we use a Consortium -> System
-> Branch setup.  Your mileage may vary a little.

   1. SELECT COUNT(acirc.id), ac.barcode AS "barcode", acl.NAME AS "shelving
   location",
   2. acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last
   circulation"
   3. FROM asset.COPY ac
   4. JOIN asset.copy_location acl ON ac.LOCATION = acl.id
   5. LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
   6. JOIN asset.call_number acn ON acn.id = ac.call_number
   7. JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
   8. JOIN actor.org_unit child ON child.id = ac.circ_lib
   9. JOIN actor.org_unit parent ON parent.id = child.parent_ou
   10. WHERE acl.NAME ILIKE 'Adult Fiction'
   11. AND acirc.xact_start IS NULL
   12. AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
   13. AND child.shortname = 'YCL-RH'
   14. AND ac.deleted = FALSE
   15. GROUP BY 2, 3, 4, 5
   16. UNION ALL
   17. SELECT COUNT(acirc.id), ac.barcode AS "barcode", acl.NAME AS "shelving
   location",
   18. acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last
   circulation"
   19. FROM asset.COPY ac
   20. JOIN asset.copy_location acl ON ac.LOCATION = acl.id
   21. LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
   22. JOIN asset.call_number acn ON acn.id = ac.call_number
   23. JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
   24. JOIN actor.org_unit child ON child.id = ac.circ_lib
   25. JOIN actor.org_unit parent ON parent.id = child.parent_ou
   26. WHERE acl.NAME ILIKE 'Adult Fiction'
   27. AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
   28. AND child.shortname = 'YCL-RH'
   29. AND ac.deleted = FALSE
   30. GROUP BY 2, 3, 4, 5
   31. HAVING MAX(acirc.xact_start) < NOW() - INTERVAL '3 years'
   32. ORDER BY 4
   33. ;

On Thu, Dec 4, 2014 at 1:25 PM, Elizabeth B. Thomsen <et at noblenet.org>
wrote:

> The tricky part of this one is the last element, finding items that were
> not checked out in the past 10 years, because you need to work with data
> which may or may not have been migrated from your old system.  Once you
> know where that is and what format it's in, you need to look for items that
> have 0 checkouts on Evergreen and whose last checkin date from your old
> system was less than 2014-12-04 (which may not be the actual date format)
> making sure that the items which were never checked in (because they were
> never checked out) are included.
>
> You also need to include the create or active date from your old system,
> because you can't blame an item for not circulating before it was added to
> the collection.  In other words, you don't want to include things that were
> added in 2013 that haven't circulated yet.
>
> The actual query depends on knowing what data you migrated, what format
> it's in and where it is.  These weeding reports that depend on working with
> data from two different systems can be tricky, but as long as you have that
> data from your old system, you ought to be able to get the information you
> need.
>
> On 12/4/2014 9:47 AM, Donald Butterworth wrote:
>
>> We do have SQL access. Stacks is a Copy location.
>>
>> On Thu, Dec 4, 2014 at 9:39 AM, Rogan Hamby <rogan.hamby at yclibrary.net
>> <mailto:rogan.hamby at yclibrary.net>> wrote:
>>
>>     Do you have SQL access?  Is Books in the Stacks a copy location?
>>
>>     On Thu, Dec 4, 2014 at 9:09 AM, Donald Butterworth
>>     <don.butterworth at asburyseminary.edu
>>     <mailto:don.butterworth at asburyseminary.edu>> wrote:
>>
>>         Colleagues,
>>
>>         We need to send a bunch of titles to offsite storage.
>>
>>         Can anyone share a report that lets us limit by Books in the
>>         Stacks, Call Number range (BS001 to BS100), Checked out zero
>>         times, over the last 10 years.
>>
>>         Thanks!
>>
>>         Don
>>
>>         --
>>         Don Butterworth
>>         Faculty Associate / Librarian III
>>         B.L. Fisher Library
>>         Asbury Theological Seminary
>>         don.butterworth at asburyseminary.edu
>>         <mailto:don.butterworth at asburyseminary.edu>
>>         (859) 858-2227 <tel:%28859%29%20858-2227>
>>
>>
>>
>>
>>     --
>>
>>     Rogan Hamby, MLS, CCNP, MIA
>>     Managers Headquarters Library and Reference Services,
>>     York County Library System
>>
>>     “You can never get a cup of tea large enough or a book long enough
>>     to suit me.”
>>     ― C.S. Lewis <http://www.goodreads.com/author/show/1069006.C_S_Lewis>
>>
>>
>>
>>
>> --
>> Don Butterworth
>> Faculty Associate / Librarian III
>> B.L. Fisher Library
>> Asbury Theological Seminary
>> don.butterworth at asburyseminary.edu
>> <mailto:don.butterworth at asburyseminary.edu>
>> (859) 858-2227
>>
>
>
> --
> Elizabeth Thomsen, Member Services Manager
> NOBLE: North of Boston Library Exchange
> 26 Cherry Hill Drive
> Danvers MA 01923
> E-mail: et at noblenet.org
>

-- 

Rogan Hamby, MLS, CCNP, MIA
Managers Headquarters Library and Reference Services,
York County Library System

“You can never get a cup of tea large enough or a book long enough to suit
me.”
― C.S. Lewis <http://www.goodreads.com/author/show/1069006.C_S_Lewis>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20141204/1e30fd8d/attachment-0001.htm>