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>
