Custom Reports: Queries for Producing Circulation Statistics for Transactions on Items
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Description
Originally posted to the Voyager Customer Listserv in 2010 by CARLI staff. No attempt was made to confirm these queries run, although the expectation is that they should. However, some modification may be required.
Provided as is. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.
Read the associated descriptive content carefully in order to understand what the queries do.
Introduction
One of the hardest things to get right in circ statistics is the counting of transactions done on items that have since been deleted. Any query involving CIRCCHARGES_VW or CIRCRENEW_VW, for example, omits such transactions. (Have I mentioned lately that I don't like these views?) Here are some of the queries from the CARLI suite of circ stats. I've tried to cover the case of items since deleted.
Queries
Title: Circulation Stat 1: Circulation by Item Location
What it does: This query is part of the annual statistics package. It counts the charges and renewals done during a specified period, broken down by the items' permanent location.
This might be useful for: Identifying the busiest parts of your collection. The Access version of this report is formatted as a table with columns for charges and renewals; you might prefer this format to the list-style format that you receive as part of the annual statistics package. Fields in the final report: item permanent location, charges/renewals, and the number of transactions done during the specified period
Version of Voyager: tested with V6.1
Author: Cathy Salika
Author's library: CARLI
Notes: If an item that circulated during the time period you specify has since been deleted, its permanent location is no longer available. Such items will appear in this query with a dummy location of "Deleted Item".
Subquery 1: Save this as "Circulation_Stat_1_Subq".
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
select 'Charges' as ctype, item_id from circ_transactions
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Charges' as ctype, item_id from circ_trans_archive
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as ctype, ct.item_id from renew_transactions rt,
circ_transactions ct
where rt.circ_transaction_id=ct.circ_transaction_id
and renew_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as ctype, ct.item_id from renew_trans_archive rt,
circ_trans_archive ct
where rt.circ_transaction_id=ct.circ_transaction_id
and renew_date between [Start Date] and [End Date];
Main Query: You can save it under any name you like, but CARLI's documentation refers to it as Circulation Stat 1: Circulation by Item Location
TRANSFORM Count(Circulation_Stat_1_Subq.ctype) AS CountOfTrans
SELECT IIf([location].[location_code] Is Null,"Deleted Item",[location].[location_code]) AS LocationCode, IIf([location].[location_name] Is Null,"Deleted Item",[location].[location_name]) AS LocationName
FROM (Circulation_Stat_1_Subq LEFT JOIN ITEM ON Circulation_Stat_1_Subq.item_id = ITEM.ITEM_ID)
LEFT JOIN LOCATION ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID
GROUP BY IIf([location].[location_code] Is Null,"Deleted Item",[location].[location_code]),
IIf([location].[location_name] Is Null,"Deleted Item",[location].[location_name])
PIVOT Circulation_Stat_1_Subq.ctype;
--------------------------------------------------------------------------------
Title: Circulation Stat 2: Circulation by Happening Location
What it does: This query is part of the annual statistics package. It counts the charges, discharges, and renewals done during a specified period, broken down by the circ happening location.
This might be useful for: Identifying the busiest circ desks. The Access version of this report is formatted as a table with columns for charges, renewals, and discharges; you might prefer this format to the list-style format that you receive as part of the annual statistics package. Fields in the final report: circ happening location, charges/discharges/renewals, and the number of transactions done during the specified period
Version of Voyager: tested with V6.1
Author: Cathy Salika
Author's library: CARLI
Notes: The Main Query prompts the user for the Start Date and End Date of the desired date range.
Subquery: Save this as "Circulation_Stat_2_Subq".
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
select 'Charges' as ctype, charge_location as HapLoc from circ_transactions
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Charges' as ctype, charge_location as HapLoc from circ_trans_archive
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as ctype, renew_location as HapLoc from renew_transactions
where renew_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as ctype, renew_location as HapLoc from renew_trans_archive
where renew_date between [Start Date] and [End Date]
UNION ALL
select 'Discharges' as ctype, discharge_location as HapLoc from
circ_trans_archive
where discharge_date between [Start Date] and [End Date];
Main Query: You can save it under any name you like, but CARLI's documentation refers to it as Circulation Stat 2: Circulation by Happening Location
TRANSFORM Count(Circulation_Stat_2_Subq.ctype) AS CountOfTrans
SELECT LOCATION.LOCATION_CODE
FROM Circulation_Stat_2_Subq INNER JOIN LOCATION
ON Circulation_Stat_2_Subq.HapLoc = LOCATION.LOCATION_ID
GROUP BY LOCATION.LOCATION_CODE
PIVOT Circulation_Stat_2_Subq.ctype;
--------------------------------------------------------------------------------
Title: Circulation Stat 3: Circulation by Patron Group/Home Library
What it does: This query is part of the annual statistics package. It counts the charges and renewals done during a specified period, broken down by the patron group and the patron's home library.
This might be useful for: For your own patrons, this gives the charges and renewals by detailed patron group. For patrons of other libraries, it gives charges and renewals by "UB" patron groups, which is a nice, simple count of resource sharing among the CARLI/I-Share libraries. The Access version of this report is formatted as a table with columns for charges and renewals; you might prefer this format to the list-style format that you receive as part of the annual statistics package.
Fields in the final report: Patron's home library database code, patron group, Charges/Renews, and count of transactions
Version of Voyager: tested with V6.1
Author: Cathy Salika
Author's library: CARLI
Date Posted: Rev. 12/1/2009
Notes: The Main Query prompts the user for the Start Date and End Date of the desired date range.
Subquery: Save this as "Circulation_Stat_3_Subq".
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
select 'Charges' as TType, ct.patron_group_id, ct.db_id
from circ_transactions ct
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Charges' as TType,ct.patron_group_id,ct.db_id
from circ_trans_archive ct
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as TType,ct.patron_group_id,ct.db_id
from renew_transactions rt, circ_transactions ct
where ct.circ_transaction_id=rt.circ_transaction_id
and renew_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as TType,ct.patron_group_id,ct.db_id
from renew_trans_archive rt, circ_trans_archive ct
where ct.circ_transaction_id=rt.circ_transaction_id
and renew_date between [Start Date] and [End Date];
Main Query: You can save it under any name you like, but CARLI's documentation refers to it as Circulation Stat 3: Circulation by Patron Group/Home Library
TRANSFORM Count(Circulation_Stat_3_Subq.TType) AS CountOfTrans
SELECT Mid([VOYAGER_DATABASES].[DB_CODE],2,3) AS DB, PATRON_GROUP.PATRON_GROUP_NAME
FROM (Circulation_Stat_3_Subq INNER JOIN PATRON_GROUP
ON Circulation_Stat_3_Subq.patron_group_id = PATRON_GROUP.PATRON_GROUP_ID)
LEFT JOIN VOYAGER_DATABASES ON Circulation_Stat_3_Subq.db_id = VOYAGER_DATABASES.DB_ID
GROUP BY Mid([VOYAGER_DATABASES].[DB_CODE],2,3), PATRON_GROUP.PATRON_GROUP_NAME
PIVOT Circulation_Stat_3_Subq.TType;
--------------------------------------------------------------------------------
Title: Circulation Stat 4: Serial Circulation
What it does: This query is part of the annual statistics package. It counts the charges (but not renewals) done during a specified period. The query identifies serials by looking for record type = 'a' and bib level = 's' in the bib record.
This might be useful for: Identifying the most- and least- used serial subscriptions. Fields in the final report: title, main entry, ISSN, and the number of charges done during the specified period
Version of Voyager: tested with V6.1
Author: Cathy Salika
Author's library: CARLI
Notes: The Main Query prompts the user for the Start Date and End Date of the desired date range.
Subquery: Save this as "Circulation_Stat_4_Subq".
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
select item_id from circ_transactions
where charge_date between [Start Date] and [End Date]
UNION ALL
select item_id from circ_trans_archive
where charge_date between [Start Date] and [End Date];
Main Query: You can save it under any name you like, but CARLI's documentation refers to it as Circulation Stat 4: Serial Circulation
SELECT utf8to16([bib_text].[TITLE]) AS Title, utf8to16([bib_text].[AUTHOR]) AS Author,
utf8to16([bib_text].[ISSN]) AS Issn, Count(Circulation_Stat_4_Subq.item_id) AS CountOfCharges
FROM Circulation_Stat_4_Subq INNER JOIN (BIB_ITEM INNER JOIN BIB_TEXT ON BIB_ITEM.BIB_ID = BIB_TEXT.BIB_ID)
ON Circulation_Stat_4_Subq.item_id = BIB_ITEM.ITEM_ID
WHERE (((BIB_TEXT.BIB_FORMAT)="as"))
GROUP BY utf8to16([bib_text].[TITLE]), utf8to16([bib_text].[AUTHOR]), utf8to16([bib_text].[ISSN]);
--------------------------------------------------------------------------------
Title: Circulation Stat 5: Comprehensive Circulation by Happening Loc
What it does: This query is part of the annual statistics package. It counts the charges, discharges, renewals, fines collected or waived, and discharges done to "route in" UB items, all for a specified period, broken down by the circ happening location.
This might be useful for: Identifying the busiest circ desks. The Access version of this report is formatted as a table with columns for charges, discharges, renewals, fines collected or waived, and UB Route Ins; you might prefer this format to the list-style format that you receive as part of the annual statistics package.
Fields in the final report: circ happening location, code and location name, charges/discharges/renewals/fines/UB Route Ins, and the number of transactions of each type done during the specified period. The count of discharges reflects real discharges; discharges done to "route in" UB items are in counted separately. The count of UB Route Ins may be a bit low. For reasons that CARLI does not yet understand, items are sometimes routed from Library A to Library B by way of Library C. The discharge that Library C does in this case is not included in the count of UB Route Ins; only the discharge at the item's destination is counted.
Version of Voyager: 2001.1.1
Author: Cathy Salika
Author's library: CARLI
Date Posted: Rev. 1/27/2009
Notes: The Main Query prompts the user for the Start Date and End Date of the date range.
Subquery: Save this as "Circulation_Stat_5_Subq".
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
select 'Charges' as TType, charge_location as HapLoc from circ_transactions
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Charges' as TType, charge_location as HapLoc from circ_trans_archive
where charge_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as TType,renew_location as HapLoc from renew_transactions
where renew_date between [Start Date] and [End Date]
UNION ALL
select 'Renews' as TType, renew_location as HapLoc from renew_trans_archive
where renew_date between [Start Date] and [End Date]
UNION ALL
select 'Discharges' as TType, discharge_location as HapLoc from
circ_trans_archive
where discharge_date between [Start Date] and [End Date]
UNION ALL
select 'Fines' as TType, trans_location as HapLoc from fine_fee_transactions
where trans_date between [Start Date] and [End Date]
UNION ALL
select 'UB Route Ins' as TType, location_id_to as HapLoc from
ub_routing_archive
where val (db_id_to) <2
and received_date between [Start Date] and [End Date];
Main Query: You can save it under any name you like, but CARLI's documentation refers to it as Circulation Stat 5: Comprehensive Circulation by Happening Location
TRANSFORM Count(Circulation_Stat_5_Subq.TType) AS CountOfTrans
SELECT IIf([LOCATION].[LOCATION_CODE] Is
Null,'off-site',[LOCATION].[LOCATION_CODE]) AS LocCode,
IIf([LOCATION].[LOCATION_NAME] Is
Null,'off-site',[LOCATION].[LOCATION_NAME]) AS LocName
FROM Circulation_Stat_5_Subq LEFT JOIN LOCATION ON
Circulation_Stat_5_Subq.HapLoc = LOCATION.LOCATION_ID
GROUP BY IIf([LOCATION].[LOCATION_CODE] Is
Null,'off-site',[LOCATION].[LOCATION_CODE]),
IIf([LOCATION].[LOCATION_NAME] Is
Null,'off-site',[LOCATION].[LOCATION_NAME])
PIVOT Circulation_Stat_5_Subq.TType;
--------------------------------------------------------------------------------
Title: Circulation Stat 6: Circulation by Patron Group and Item Type
What it does: This query is part of the annual statistics package. It counts the charges done during a specified period, broken down by the patron group and the item type that the item had at the time the charge was done.
This might be useful for: Analyzing collection use. If your library uses temporary item types for reserves or new books, the use of these materials will show up. The Access version of this report is formatted as a table with rows for patron group and columns for item type; you might prefer this format to the list-style format that you receive as part of the annual statistics package.
Fields in the final report: patron group code, patron group name, item type code, and the number of charge transactions done during the specified period
Version of Voyager: tested with V6.5
Author: Cathy Salika
Author's library: CARLI
Date Posted: 6/27/2008
Notes: If the patron group has been deleted from your policies since the charge transaction was done, the patron group code and name will display as "deleted" and "deleted patron group".
Notes: This query determines the item type by going through your circ policy matrix. If the charge was done under one of your "all,all" rules, the item type will display as "default".
Subquery: This involves a query and a subquery. Here is the subquery. Save it under the name, "Circulation_Stat_6_Subq", but don't run it.
PARAMETERS [Start Date:] DateTime, [End Date:] DateTime;
SELECT
IIf([patron_group].[patron_group_code] Is Null,'deleted',[patron_group].[patron_group_code]) AS PatronGroup,
IIf([patron_group].[patron_group_name] Is Null,'deleted patron group',[patron_group].[patron_group_name]) AS PatronGroupName,
IIf([item_type].[item_type_code] Is Null,'default',[item_type].[item_type_code]) AS ItemType,
Count(CIRC_TRANSACTIONS.CIRC_TRANSACTION_ID) AS
CountOfCIRC_TRANSACTION_ID
FROM PATRON_GROUP RIGHT JOIN ((CIRC_POLICY_MATRIX LEFT JOIN ITEM_TYPE ON
CIRC_POLICY_MATRIX.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID)
INNER JOIN CIRC_TRANSACTIONS
ON CIRC_POLICY_MATRIX.CIRC_POLICY_MATRIX_ID = CIRC_TRANSACTIONS.CIRC_POLICY_MATRIX_ID)
ON PATRON_GROUP.PATRON_GROUP_ID = CIRC_TRANSACTIONS.PATRON_GROUP_ID
WHERE (((CIRC_TRANSACTIONS.CHARGE_DATE) Between [Start Date:] And [End Date:]))
GROUP BY
IIf([patron_group].[patron_group_code] Is Null,'deleted',[patron_group].[patron_group_code]),
IIf([patron_group].[patron_group_name] Is Null,'deleted patron group',[patron_group].[patron_group_name]),
IIf([item_type].[item_type_code] Is Null,'default',[item_type].[item_type_code])
UNION ALL SELECT
IIf([patron_group].[patron_group_code] Is Null,'deleted',[patron_group].[patron_group_code]) AS PatronGroup,
IIf([patron_group].[patron_group_name] Is Null,'deleted patron group',[patron_group].[patron_group_name]) AS PatronGroupName,
IIf([item_type].[item_type_code] Is Null,'default',[item_type].[item_type_code]) AS ItemType,
Count(CIRC_TRANS_ARCHIVE.CIRC_TRANSACTION_ID) AS CountOfCIRC_TRANSACTION_ID
FROM PATRON_GROUP RIGHT JOIN ((CIRC_POLICY_MATRIX LEFT JOIN ITEM_TYPE
ON CIRC_POLICY_MATRIX.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID)
INNER JOIN CIRC_TRANS_ARCHIVE
ON CIRC_POLICY_MATRIX.CIRC_POLICY_MATRIX_ID = CIRC_TRANS_ARCHIVE.CIRC_POLICY_MATRIX_ID)
ON PATRON_GROUP.PATRON_GROUP_ID = CIRC_TRANS_ARCHIVE.PATRON_GROUP_ID
WHERE (((CIRC_TRANS_ARCHIVE.CHARGE_DATE) Between [Start Date:] And [End Date:]))
GROUP BY
IIf([patron_group].[patron_group_code] Is Null,'deleted',[patron_group].[patron_group_code]),
IIf([patron_group].[patron_group_name] Is Null,'deleted patron group',[patron_group].[patron_group_name]),
IIf([item_type].[item_type_code] Is Null,'default',[item_type].[item_type_code]);
Main Query: Here is the main query. Save it under the name, "Circulation Stat 6: Charges by Item Type and Patron Group" and run it.
TRANSFORM Sum(Circulation_Stat_6_Subq.CountOfCIRC_TRANSACTION_ID)
AS SumOfCountOfCIRC_TRANSACTION_ID
SELECT Circulation_Stat_6_Subq.PatronGroup, Circulation_Stat_6_Subq.PatronGroupName
FROM Circulation_Stat_6_Subq
GROUP BY Circulation_Stat_6_Subq.PatronGroup, Circulation_Stat_6_Subq.PatronGroupName
PIVOT Circulation_Stat_6_Subq.ItemType;
----------------------------------------------------------------
Title: Circulation Stat 7: Charges by Circ Hap Loc Item Type & Item Loc
What it does: Prompts for a starting and ending date, then counts the charge transactions by circ happening loc, item type, and item loc
This might be useful for: Annual statistics, especially reserves circ
Fields in the final report: Circ happening location, item type, item
location code, item location name
Version of Voyager: V6.5
Author: Cathy Salika
Author's library: CARLI
Date Posted: 1/27/2009
Notes: The query was designed to help locate reserves charges, but it may be useful for other things. Some libraries charge reserves from a distinctive circ happening location, some assign reserves a distinctive item type, and some assign reserves a distinctive location. In this query, the circ happening location and item type are the values that were in effect at the time of the charge. An item type of 'default' indicates that the item type cannot be determined from the circ policies. The location code and name are the item's current location, the temp location if there is one, otherwise the permanent location. If the item has been deleted since the charge, the location will be 'deleted'.
Main Query:
SELECT CIRC_POLICY_GROUP.CIRC_GROUP_NAME AS CircGroup,
IIf([item_type].[item_type_id] Is Null,'default',[item_type].[item_type_code]) AS ItemType,
IIf([location_1].[location_code] Is Not
Null,[location_1].[location_code],IIf([location].[location_code] Is Not
Null,[location].[location_code],'deleted')) AS ItemCurrentLocCode,
IIf([location_1].[location_name] Is Not
Null,[location_1].[location_name],IIf([location].[location_name] Is Not
Null,[location].[location_name],'deleted item')) AS ItemCurrentLocName,
Count([Circulation Transactions (Charges)].CIRC_TRANSACTION_ID) AS Charges
FROM ((((([Circulation Transactions (Charges)] INNER JOIN CIRC_POLICY_MATRIX
ON [Circulation Transactions (Charges)].CIRC_POLICY_MATRIX_ID = CIRC_POLICY_MATRIX.CIRC_POLICY_MATRIX_ID)
INNER JOIN CIRC_POLICY_GROUP ON CIRC_POLICY_MATRIX.CIRC_GROUP_ID = CIRC_POLICY_GROUP.CIRC_GROUP_ID)
LEFT JOIN ITEM ON [Circulation Transactions (Charges)].ITEM_ID = ITEM.ITEM_ID) LEFT JOIN LOCATION
ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID) LEFT JOIN LOCATION AS LOCATION_1
ON ITEM.TEMP_LOCATION = LOCATION_1.LOCATION_CODE) LEFT JOIN ITEM_TYPE
ON CIRC_POLICY_MATRIX.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID
WHERE ((([Circulation Transactions (Charges)].CHARGE_DATE) Between
[Start Date:] And [End Date:]))
GROUP BY CIRC_POLICY_GROUP.CIRC_GROUP_NAME,
IIf([item_type].[item_type_id] Is
Null,'default',[item_type].[item_type_code]),
IIf([location_1].[location_code] Is Not
Null,[location_1].[location_code],IIf([location].[location_code] Is Not
Null,[location].[location_code],'deleted')),
IIf([location_1].[location_name] Is Not
Null,[location_1].[location_name],IIf([location].[location_name] Is Not
Null,[location].[location_name],'deleted item'))
ORDER BY CIRC_POLICY_GROUP.CIRC_GROUP_NAME,
IIf([item_type].[item_type_id] Is
Null,'default',[item_type].[item_type_code]),
IIf([location_1].[location_code] Is Not
Null,[location_1].[location_code],IIf([location].[location_code] Is Not
Null,[location].[location_code],'deleted'));
- Article last edited: 20-Jan-2021