[OPEN-ILS-GENERAL] Connecting circ transactions to changing patron profile

[Melissa Belvadi]

Hi, all.

We are an academic library who also offers "public" memberships to members
of the community, with somewhat more restricted borrowing privileges than
students have.  When students graduate, they are automatically converted to
the "public" profile (literally changing their "profile" code in actor.usr)
via batch update.  They keep the same barcode/id#, not assigned new cards,
and thus the same id# in Evergreen. We currently have no expiration on
public memberships and no procedure for doing so yet.

We are now faced with wanting to know how many "active" members of the
public we have, defining "active" as "users who have had some circ
transaction activity within the last three years".

The problem is that so far as I can tell, there is no record in the
Evergreen tables as to when a user's profile code is changed. So if I just
look in actor.usr at everyone who is a member of the "public" profile as of
this moment, and find all of the transactions within the last 3 years in
action.circulation that are connected to those users (a very simple sql
query), I'll get mostly students who graduated within the last three years,
whose circ activity took place when they were students, not as members of
the "public".

Am I missing something? Is there a table/field somewhere that can help me
"join" so that I only retrieve those circ transactions that were done by
users who were in the "public" profile" *at the time of the transaction*
regardless of what that same user's profile assignment is at the time I'm
running the query?


Thanks for any help!

-- 
Melissa Belvadi
User Experience & Collections Librarian
University of Prince Edward Island
mbelvadi at upei.ca 902-566-0581
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20111118/dfcca66f/attachment.htm>