Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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
    • Was this article helpful?