<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000">Thank you for sharing this, Galen. </div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000">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.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000">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:</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000"><i>SELECT AVG("Renewals")<br>FROM<br>(<br>WITH RECURSIVE chain_circ AS <br>        (<br>        SELECT <a href="http://circ.id" target="_blank">circ.id</a> as first_circ, circ.*<br>        FROM action.all_circulation_slim circ<br>        WHERE circ.parent_circ IS NULL <br>            <span style="background-color:rgb(255,242,204)">AND circ.xact_start::date>='2023-05-01'</span><br>        UNION ALL<br>        SELECT first_circ, circ2.*<br>        FROM action.all_circulation_slim circ2<br>            JOIN chain_circ ON circ2.parent_circ=<a href="http://chain_circ.id" target="_blank">chain_circ.id</a><br>        )<br>SELECT first_circ as "Initial Circ", count(distinct id)-1 as "Renewals"<br>FROM chain_circ<br>GROUP BY 1<br>ORDER BY 2 <br>) recur_circ</i><br><br>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.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000">John</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small;color:#000000"><table style="font-family:Arial,Helvetica,sans-serif;color:rgb(80,0,80);font-size:12.8px;border:none;border-collapse:collapse"><tbody><tr style="height:0pt"><td style="border-right:0.5pt solid rgb(0,0,0);vertical-align:top;padding:5pt"><p><img width="96" height="63" src="https://ci3.googleusercontent.com/mail-sig/AIorK4wvJisWKEcU1hp3aB6DvlIiGEoGe1GB9N5Bd_3T9VrHtqvFZ2MKH6Iyyzn0PTTeUWUJ5cCNNec"></p></td><td style="border-left:0.5pt solid rgb(0,0,0);vertical-align:top;padding:5pt"><p dir="ltr" style="line-height:1.9872;margin-top:0pt;margin-bottom:0pt"><span style="font-size:10pt;font-family:Verdana;color:rgb(0,0,0);background-color:transparent;vertical-align:baseline">John Amundson | Library Applications Supervisor | CW MARS</span></p><p dir="ltr" style="line-height:1.9872;margin-top:0pt;margin-bottom:0pt"><span style="font-size:10pt;font-family:Verdana;vertical-align:baseline;background-color:transparent"><font color="#000000"><a href="mailto:jamundson@cwmars.org" target="_blank">jamundson@cwmars.org</a> | <a href="tel:%28508%29%20755-3323" target="_blank">508-755-3323 x322</a></font></span></p><p dir="ltr" style="line-height:1.9872;margin-top:0pt;margin-bottom:0pt"><span style="font-size:10pt;font-family:Verdana;vertical-align:baseline;background-color:transparent"><font color="#000000"><a href="https://www.cwmars.org/" target="_blank">https://www.cwmars.org</a></font></span></p><p dir="ltr" style="line-height:1.9872;margin-top:0pt;margin-bottom:0pt"><span style="font-size:10pt;font-family:Verdana;vertical-align:baseline;background-color:transparent"><a href="https://www.mypronouns.org/he-him" target="_blank">he/him/his</a></span></p></td></tr></tbody></table></div><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div dir="ltr"><div dir="ltr"><a href="http:///" target="_blank"></a></div></div></div></div></div><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jun 1, 2023 at 2:37 PM Galen Charlton via Evergreen-reports <<a href="mailto:evergreen-reports@list.evergreen-ils.org" target="_blank">evergreen-reports@list.evergreen-ils.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hi,<br><br>On Thu, Jun 1, 2023 at 1:43 PM Elizabeth Davis via Evergreen-reports <<a href="mailto:evergreen-reports@list.evergreen-ils.org" target="_blank">evergreen-reports@list.evergreen-ils.org</a>> wrote:<br><div>> I am interested in creating a report template to find the average <br></div><div>> number of renewals used as part of a check out.  Has anyone</div><div>> ever done something like this?</div><br></div><div>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.</div><div><br></div><div>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:</div><div><br></div><div>-- summarize the circulation chains and put them in a temporary table<br></div><div>CREATE TEMPORARY TABLE circ_chains</div><div>AS SELECT action.summarize_circ_chain(id) <br></div><div>FROM action.circulation <br></div><div>WHERE parent_circ IS null</div><div>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<br></div><div><br></div><div>-- get the average number of renewals</div><div>SELECT AVG((summarize_circ_chain).num_circs - 1)AS average_renewals</div><div>FROM circ_chains;</div><div><br></div><div>Regards,</div><div><br></div><div>Galne<br></div><div>--<br>Galen Charlton<br>Implementation and IT Manager<br>Equinox Open Library Initiative<br>gmc@equinoxOLI.org<br><a href="https://www.equinoxOLI.org" target="_blank">https://www.equinoxOLI.org</a><br>phone: 877-OPEN-ILS (673-6457)<br>direct: 770-709-5581</div></div>
_______________________________________________<br>
Evergreen-reports mailing list<br>
<a href="mailto:Evergreen-reports@list.evergreen-ils.org" target="_blank">Evergreen-reports@list.evergreen-ils.org</a><br>
<a href="http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports" rel="noreferrer" target="_blank">http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports</a><br>
</blockquote></div>