There was a request today for a Voyager report on a listserv. Here it is.
I'm trying to compare library usage from Fall 08 semester to Fall 09 semeseter. One of the things I'd like to do is show how many patrons checked items out of the library during that time period.
- If the same patron checked items out on 5 DIFFERENT days during the month of January, I'd like to count that patron 5 times.
- If the same patron checked out multiple items on the SAME day, I'd like to count that patron once for that day.
Does anyone have an access report that they use to show circulation usage that they could share?
I whipped up a quick report, but it doesn't run in MS Access. Instead, you must use sqlplus or any other Oracle driver.
-- This report gives a count of the number of patrons initiating transactions
-- each day. This is a good measurement of the number of unique patrons
-- circulating items rather than a raw circulation count.
------------------------------------------------------------------------------
-- Date Grouping
-- Note: only one of these variables should be uncommented.
--DEFINE _GROUP_DATES = 'YYYY-MM-DD' -- Group by day
--DEFINE _GROUP_DATES = 'YYYY-MM' -- Group by month
DEFINE _GROUP_DATES = 'YYYY' -- Group by year
------------------------------------------------------------------------------
-- Date range.
-- Note: that dates default to a time of 00:00:00 (midnight). So you should
-- probably add one to your end date.
DEFINE _DATE_BEGIN = '01-JAN-08' -- DD-MON-YY
DEFINE _DATE_END = '01-JAN-09' -- DD-MON-YY
SELECT
count(patron_id) as "Count"
, charge_date as "Date"
FROM
(SELECT UNIQUE
patron_id
, to_char(to_date(charge_date, 'YYYY-MM-DD'), '&_GROUP_DATES') as charge_date
FROM
((SELECT UNIQUE
patron_id
, to_char(charge_date, 'YYYY-MM-DD') as charge_date
FROM
circ_trans_archive
WHERE
charge_date between '&_DATE_BEGIN' and '&_DATE_END')
union
(SELECT UNIQUE
patron_id
, to_char(charge_date, 'YYYY-MM-DD') as charge_date
FROM
circ_transactions
WHERE
charge_date between '&_DATE_BEGIN' and '&_DATE_END'))
)
GROUP BY
charge_date
ORDER BY
2;
UNDEF _GROUP_DATES
UNDEF _DATE_BEGIN
UNDEF _DATE_END
The general idea is to union the active transactions with the archived transactions. We also convert the charge date to a string of only its date (no time). This will allow the unique operation to remove duplicate patrons on any given day. We can then group by year, month, or date as configured.
Here is some sample output:
By date:
Count Date
---------- ----------
232 2008-12-01
237 2008-12-02
242 2008-12-03
233 2008-12-04
144 2008-12-05
41 2008-12-06
17 2008-12-07
222 2008-12-08
248 2008-12-09
242 2008-12-10
247 2008-12-11
134 2008-12-12
27 2008-12-13
27 2008-12-14
156 2008-12-15
146 2008-12-16
128 2008-12-17
113 2008-12-18
1 2008-12-19
5 2008-12-20
20 2008-12-22
22 2008-12-23
13 2008-12-24
7 2008-12-26
28 2008-12-29
27 2008-12-30
8 2008-12-31
By month:
Count Date
---------- -------
747 2008-01
1856 2008-02
1704 2008-03
1821 2008-04
1510 2008-05
510 2008-06
554 2008-07
419 2008-08
2045 2008-09
2049 2008-10
1829 2008-11
1636 2008-12
By year:
Count Date
---------- ----
6702 2008
Please note that this report only works if you have elected to retain patron ids for circulation history. This is an option in the module SysAdmin > System > "Retain patron id for circ history".
I've done my best to make the report easy to modify by using variables instead of embedding magic numbers.