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 <[email protected]> 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 <[email protected]> > 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 <[email protected] >>> <mailto:[email protected]>> 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 >>> <[email protected] >>> <mailto:[email protected]>> 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 >>> [email protected] >>> <mailto:[email protected]> >>> (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 >>> [email protected] >>> <mailto:[email protected]> >>> (859) 858-2227 >>> >> >> >> -- >> Elizabeth Thomsen, Member Services Manager >> NOBLE: North of Boston Library Exchange >> 26 Cherry Hill Drive >> Danvers MA 01923 >> E-mail: [email protected] >> > > > > -- > > 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 [email protected] (859) 858-2227
