[OPEN-ILS-GENERAL] Reminder for Conference proposals

[Rogan Hamby]

Rest, don't check listservs!

On Thu, Feb 19, 2015 at 10:14 AM, Lindsay Stratton <lstratton at pls-net.org>
wrote:

> Ooooooo! Thanks, Rogan!
>
> Our libraries are small; the largest collections, adult fiction, are less
> than 8,000 items.
>
> I look forwarded to playing with that code (when I am not recovering from
> flu)!
>
> Lindsay Stratton
> Library Automation Services Manager
> Pioneer Library System
> 2557 State Rte 21
> Canandaigua, NY 14424
> 585-394-8260 x104
>
>
> ------------------------------
>
> *From: *"Rogan Hamby" <rogan.hamby at yclibrary.net>
> *To: *"Evergreen Discussion Group" <
> open-ils-general at list.georgialibraries.org>
> *Sent: *Wednesday, February 18, 2015 5:00:30 PM
> *Subject: *Re: [OPEN-ILS-GENERAL] Reminder for Conference proposals
>
> I probably wouldn't manage the whole conference but I'd be glad to sit
> around and pound on keys like the proverbial 10 thousand monkeys with
> others and see what happens.  :)
>
> The SQL to look at the stats you're talking about with circs per year is
> something I'd have to tease out.  I'd be concerned about it if it hits a
> really large collection.  Depending on how you want to do it there are a
> few options.  If you wanted to extract and group the year of the circs,
> that's easy.
>
> If you want a count per increment of 1 years from date it's added that's a
> bit different.  Here is a quick and dirty (and inelegant) way to do that
> for a single copy:
>
> select count(id) as circ_count, circ_year
> from
> ( select ac.id,
> case when acirc.xact_start < ac.create_date + interval '1 year' then '1'
>  when acirc.xact_start < ac.create_date + interval '2 years' and
> acirc.xact_start > ac.create_date + interval '1 year' then '2'
>  when acirc.xact_start < ac.create_date + interval '3 years' and
> acirc.xact_start > ac.create_date + interval '2 years' then '3'
>  when acirc.xact_start < ac.create_date + interval '4 years' and
> acirc.xact_start > ac.create_date + interval '3 years' then '4'
>  when acirc.xact_start < ac.create_date + interval '5 years' and
> acirc.xact_start > ac.create_date + interval '4 years' then '5'
> end
> as circ_year
> from asset.copy ac
> left join action.circulation acirc on acirc.target_copy = ac.id
> join asset.copy_location acl on acl.id = ac.location
> where ac.id = 3325389
> ) steed
> group by 2
> order by 2 asc
> ;
>
> That could be made more elegant and made into a version for shelving
> locations and results averaged.  For example if I was making it more
> elegant I wouldn't make a hand written case but I'd have it do a loop based
> on substracting creation dates from last circ dates.
>
>
>
>
>
>
>


-- 

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/20150219/1eac5256/attachment.html>