<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1027" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">Thanks Galen! I had a feeling it would be more of the SQL query situation.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="vertical-align:baseline"><!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f">
<v:stroke joinstyle="miter" />
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />
<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_s1026" type="#_x0000_t75" style='position:absolute;margin-left:0;margin-top:.15pt;width:62.25pt;height:62.25pt;z-index:251659264;visibility:visible;mso-wrap-style:square;mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;mso-position-horizontal-relative:text;mso-position-vertical:absolute;mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="cid:image001.png@01D99497.7A261930" o:title="" />
<w:wrap type="square"/>
</v:shape><![endif]--><![if !vml]><img width="83" height="83" style="width:.8645in;height:.8645in" src="cid:image003.png@01D99497.7A294D80" align="left" hspace="12" v:shapes="Picture_x0020_1"><![endif]><b><span style="color:#16697A">Elizabeth
Davis</span></b><b> </b>(she/her), <b><i><span style="color:#16697A">Support & Project Management Specialist</span></i></b><span style="font-size:12.0pt;color:#16697A"> </span><span style="font-size:9.0pt;font-family:"Segoe UI",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal" style="vertical-align:baseline"><b><span style="font-size:12.0pt;color:#3366CC">Pennsylvania Integrated Library System
</span></b><b><span style="color:#4472C4">(PaILS) | SPARK</span></b><span style="font-size:12.0pt;color:#3366CC"> </span><span style="font-size:9.0pt;font-family:"Segoe UI",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal" style="vertical-align:baseline"><span style="color:#3366CC">(717) 256-1627</span> |
<a href="mailto:katherine.dannehl@sparkpa.org" target="_blank"><span style="color:#0563C1">elizabeth.davis@sparkpa.org</span></a> <br>
<a href="https://support.sparkpa.org/" target="_blank"><span style="color:#0563C1">support.sparkpa.org</span></a><span style="color:#3366CC"> |
</span><a href="mailto:support@sparkpa.org" target="_blank"><span style="color:#0563C1">support@sparkpa.org</span></a><span style="color:#3366CC"> </span><span style="font-size:9.0pt;font-family:"Segoe UI",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Galen Charlton <gmc@equinoxoli.org> <br>
<b>Sent:</b> Thursday, June 1, 2023 2:37 PM<br>
<b>To:</b> Evergreen Community Reports Email List <evergreen-reports@list.evergreen-ils.org><br>
<b>Cc:</b> Elizabeth Davis <elizabeth.davis@sparkpa.org><br>
<b>Subject:</b> Re: [Evergreen-reports] Average Renewals used<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">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">evergreen-reports@list.evergreen-ils.org</a>> wrote:<o:p></o:p></p>
<div>
<p class="MsoNormal">> I am interested in creating a report template to find the average
<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">> number of renewals used as part of a check out. Has anyone<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">> ever done something like this?<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">-- summarize the circulation chains and put them in a temporary table<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">CREATE TEMPORARY TABLE circ_chains<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">AS SELECT action.summarize_circ_chain(id) <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM action.circulation <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">WHERE parent_circ IS null<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">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<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">-- get the average number of renewals<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">SELECT AVG((summarize_circ_chain).num_circs - 1)AS average_renewals<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM circ_chains;<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Regards,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Galne<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">--<br>
Galen Charlton<br>
Implementation and IT Manager<br>
Equinox Open Library Initiative<br>
<a href="mailto:gmc@equinoxOLI.org">gmc@equinoxOLI.org</a><br>
<a href="https://www.equinoxOLI.org">https://www.equinoxOLI.org</a><br>
phone: 877-OPEN-ILS (673-6457)<br>
direct: 770-709-5581<o:p></o:p></p>
</div>
</div>
</div>
</body>
</html>