[Evergreen-reports] Average Renewals used

[Elizabeth Davis]

Thanks Galen! I had a feeling it would be more of the SQL query situation.


[cid:image003.png at 01D99497.7A294D80]Elizabeth Davis (she/her), Support & Project Management Specialist
Pennsylvania Integrated Library System (PaILS) | SPARK
(717) 256-1627 | elizabeth.davis at sparkpa.org<mailto:katherine.dannehl at sparkpa.org>
support.sparkpa.org<https://support.sparkpa.org/> | support at sparkpa.org<mailto:support at sparkpa.org>

From: Galen Charlton <gmc at equinoxoli.org>
Sent: Thursday, June 1, 2023 2:37 PM
To: Evergreen Community Reports Email List <evergreen-reports at list.evergreen-ils.org>
Cc: Elizabeth Davis <elizabeth.davis at sparkpa.org>
Subject: Re: [Evergreen-reports] Average Renewals used

Hi,

On Thu, Jun 1, 2023 at 1:43 PM Elizabeth Davis via Evergreen-reports <evergreen-reports at list.evergreen-ils.org<mailto:evergreen-reports at list.evergreen-ils.org>> wrote:
> I am interested in creating a report template to find the average
> number of renewals used as part of a check out.  Has anyone
> ever done something like this?

I don't think there's a reporting source that can do this directly, nor can the reporter follow the chain of renewals for a given loan directly.

However, there are stored procedures that can summarize the circulation chain for each loan, so for a one-shot report the following could be done via SQL:

-- summarize the circulation chains and put them in a temporary table
CREATE TEMPORARY TABLE circ_chains
AS SELECT action.summarize_circ_chain(id)
FROM action.circulation
WHERE parent_circ IS null
AND xact_start >= '2023-05-01'; -- or whatever; note that summarizing the circ chains for all loans in a large database will take a while

-- get the average number of renewals
SELECT AVG((summarize_circ_chain).num_circs - 1)AS average_renewals
FROM circ_chains;

Regards,

Galne
--
Galen Charlton
Implementation and IT Manager
Equinox Open Library Initiative
gmc at equinoxOLI.org<mailto:gmc at equinoxOLI.org>
https://www.equinoxOLI.org
phone: 877-OPEN-ILS (673-6457)
direct: 770-709-5581
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20230601/fdb97069/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 10034 bytes
Desc: image001.png
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20230601/fdb97069/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 5402 bytes
Desc: image003.png
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20230601/fdb97069/attachment-0003.png>