That code misses some cases. You'll need to catch cases where the user was
only edited before the date range, but their profile in the most recent edit is
the one needed. And you'll need to catch cases where the user was only edited
after the date range, but their profile in the oldest edit is the one needed.
Liam
________________________________
From: [email protected]
[mailto:[email protected]] On Behalf Of
Whalen, Liam
Sent: November 18, 2011 14:39
To: Evergreen Discussion Group
Subject: Re: [OPEN-ILS-GENERAL] Connecting circ transactions to
changingpatronprofile
You could use auditor.actor_usr_history. This code will probably need
some tweaking.
/*users where they had the required profile between the required dates
*/
WHERE usr.id IN (SELECT DISTINCT usr.id
FROM auditor.actor_usr_history AS aauh
WHERE aauh.profile = <id>
AND aauh.audit_time BETWEEN <date1> AND
<date2>)
/*users who have only ever had the required profile*/
OR usr.id IN (SELECT DISTINCT au.id
FROM actor.usr AS au
WHERE au.id NOT IN (SELECT DISTINCT aauh.id
FROM
auditor.actor_usr_history AS aauh)
AND au.profile = <id>)
Liam
Natural Resources Canada Library / Bibliothèque de Ressources
naturelles Canada
615 rue Booth Street, 121, Ottawa, ON
Canada K1A 0E9
Telephone / Téléphone : 613-995-4180
[email protected]
Web: http://www.nrcan.gc.ca/library/
________________________________
From: [email protected]
[mailto:[email protected]] On Behalf Of
Melissa Belvadi
Sent: November 18, 2011 14:21
To: Evergreen Discussion Group
Subject: [OPEN-ILS-GENERAL] Connecting circ transactions to
changing patronprofile
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
[email protected] 902-566-0581
