[OPEN-ILS-GENERAL] Reminder for Conference proposals

[Lindsay Stratton]

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 

----- Original Message -----

> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20150219/d0375680/attachment-0001.html>