[OPEN-ILS-GENERAL] ***SPAM*** Re: Call Number/Checkout report

[Donald Butterworth]

Many, many thanks Rogan,

I will pass this on to a SQL wizard. We tried pretty hard to migrate values
that could be used for this kind of report.

Don

On Thu, Dec 4, 2014 at 1:33 PM, Rogan Hamby <rogan.hamby at yclibrary.net>
wrote:

> 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>
>



-- 
Don Butterworth
Faculty Associate / Librarian III
B.L. Fisher Library
Asbury Theological Seminary
don.butterworth at asburyseminary.edu
(859) 858-2227
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20141204/94480d5c/attachment-0001.htm>