[Evergreen-reports] Average Renewals used

[John Amundson]

Thank you for sharing this, Galen.

I've wanted to see the chain of renewals on a circulation before in the
past but could never quite figure out how. Good to know there are stored
procedures.

Your response made me want to tackle this again though, and I put together
this query that averages the renewal count per initial checkout over a
given time period using a recursive CTE:



















*SELECT AVG("Renewals")FROM(WITH RECURSIVE chain_circ AS         (
SELECT circ.id <http://circ.id> as first_circ, circ.*        FROM
action.all_circulation_slim circ        WHERE circ.parent_circ IS NULL
        AND circ.xact_start::date>='2023-05-01'        UNION ALL
SELECT first_circ, circ2.*        FROM action.all_circulation_slim circ2
        JOIN chain_circ ON circ2.parent_circ=chain_circ.id
<http://chain_circ.id>        )SELECT first_circ as "Initial Circ",
count(distinct id)-1 as "Renewals"FROM chain_circGROUP BY 1ORDER BY 2 )
recur_circ*

It seems to work in my network, and I thought it might be useful to others
who do not want to create a temporary table.

John

John Amundson | Library Applications Supervisor | CW MARS

jamundson at cwmars.org | 508-755-3323 x322 <%28508%29%20755-3323>

https://www.cwmars.org

he/him/his <https://www.mypronouns.org/he-him>


On Thu, Jun 1, 2023 at 2:37 PM Galen Charlton via Evergreen-reports <
evergreen-reports at list.evergreen-ils.org> wrote:

> Hi,
>
> On Thu, Jun 1, 2023 at 1:43 PM Elizabeth Davis via Evergreen-reports <
> 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
> https://www.equinoxOLI.org
> phone: 877-OPEN-ILS (673-6457)
> direct: 770-709-5581
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20230601/6c95d81f/attachment.htm>