[OPEN-ILS-GENERAL] Reminder for Conference proposals

Rogan Hamby rogan.hamby at yclibrary.net
Wed Feb 18 17:00:30 EST 2015


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.





On Wed, Feb 18, 2015 at 1:42 PM, Lindsay Stratton <lstratton at pls-net.org>
wrote:

> I could spend the entire conference happily monkeying with SQL with
> company... just sayin...
>
> I am working on collection management reports - not just weeding (this
> item is x years old, circ'd x times, blah blah) but more interesting data
> about how materials are being used. I'm also working with
> fine/billing/money reports. In additional to concrete help with my own
> queries I'd also love to know what others are doing. I am also interested
> in talking about data sources, and what sort of care one must take when
> selecting a specific source, and various report criteria. This is
> particularly critical in anything money related.
>
> A specific query that has me stumped (my SQL is rudimentary) - I want to
> know, by collection, how many times an item has circulated in the 1st year
> after purchase, 2nd year, and so forth. I have no idea how to make SQL do
> the math for a single example, much less account for the collection level
> view, where bibs/items are continuously added/deleted, etc.
>
> Related to circ stats, I am also interested in trying to capture data that
> indicates the value to the community that "free" library materials provide.
> Many libraries have set up "You saved $x.xx checking out books from the
> library today!" statements on check out slips, and we want to take that to
> the next level as some kind of eye catching data point for community
> reports.
>
>
> 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 11, 2015 8:52:58 PM
> *Subject: *Re: [OPEN-ILS-GENERAL] Reminder for Conference proposals
>
> If I do a hands on workshop (I threw a rough proposal up) what kinds of
> sql tasks would folks like to step through as learning examples?
>
>
>


-- 

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/20150218/b61b1536/attachment.html>


More information about the Open-ils-general mailing list