Finding average length of loan and number of items checked out at a time

Description

We need to produce a report for a specific title (with 135 items attached) for the last fiscal year (July 1, 2017 – June 30, 2018):

 

  Avg length of loan (check out to return)

  Avg number checked out at a time

  Max number checked out at a time

Resolution

 

[Courtesy of Hans Breitenlohner, University of Maryland...]     If you are interested in full days only:

 

SQL>  select ...  to_date(z36h_returned_date,'YYYYMMDD') - to_date(z36h_loan_date,'YYYYMMDD') ...

 

If you want to include the time:

 

SQL>   select ... to_date(z36h_returned_date*10000+z36h_returned_hour,'YYYYMMDDHH24MI') -

              to_date(z36h_loan_date*10000+z36h_loan_hour,'YYYYMMDDHH24MI')   ....

 

The result is in units of full days. (1 hour = .041666, 1 minute = .00069444)

 

To get the number of loans at a given time, you could try something like the following:

 

create a table or view of only the z36h transactions of interest, I'll call it x

 

select starttime,

   (select count(*) from x

    where starttime >= z36h_loan_date*10000 + z36h_loan_hour

    and  starttime <= z36h_returned_date*10000 + z36h_returned_hour) numloans from ( select z36h_loan_date*10000 + z36h_loan_hour starttime from x);

 

<end Hans B. response>

 

The following may be useful for a general picture of loans of the items for this title in this time period:

 

SQL> select substr (z36h_rec_key,1,15), z36h_loan_date, z36h_loan_hour, z36h_returned_date,  z36h_returned_hour from z36h

where z36h_rec_key like '000123456%' and z36h_loan_date >= '20170701' and  z36h_loan_date <= '20180630'

order by substr (z36h_rec_key,1,15), z36h_loan_date, z36h_loan_hour, z36h_returned_date, z36h_returned_hour;

 

 

In regard to the number of items for this title "checked out at a time", we have created the following SQL based on "within the same hour".  It doesn't have the average or max, but I think gives a pretty good picture.... 

 

SQL>  select z36h_loan_date, substr (z36h_loan_hour,1,2), count(*) from z36h where z36h_rec_key like '000123456%' and z36h_loan_date >= '20170701' and  z36h_loan_date <= '20180630' group by z36h_loan_date, substr (z36h_loan_hour,1,2) having count (*) > 1  order by count(*); 

 

 

The following might be of some use for the average length of reserve-item loans (in hours).  It’s limited to the cases where the item is returned on the same day, and it doesn’t do anything with minutes, but might be of some use:

 

1.  SQL>  select sum (substr (z36h_returned_hour,1,2)) - sum (substr (z36h_loan_hour,1,2) ) from z36h where z36h_rec_key like '000123456%' and z36h_returned_date = z36h_loan_date  and z36h_loan_date >= '20170701' and  z36h_loan_date <= '20180630';

2. SQL>  select count(*) from z36h where z36h_rec_key like '000123456%' and z36h_returned_date = z36h_loan_date and z36h_loan_date >= '20170701' and  z36h_loan_date <= '20180630';

 

{Then you’d divide the result of #1 by the result of #2 to get the average (in hours).}

 

 

---

• Article last edited: 18-Jul-2018