Average Loan Time by Collection

Clearinghouse Repository Polaris Repository
,
1

Average Loan Time by Collection

This was a contribution from someone but damned if I can figure out who. If this is your code, let me know! It pulls the average loan time by collection - so you’re going to get the average number of days items in that collection stay checked out.

declare @startDate date = '2021-09-01 00:00:00.000'
declare @endDate date = '2022-05-31 23:59:59.999'

select
    d.Collection
    ,avg(d.LoanDays) [AvgLoanDays]
    ,min(d.LoanDays) [MinLoanDays]
    ,max(d.LoanDays) [MaxLoandays]
    ,count(*) [NumItemsNotEntirelyAccurate]
from 
    (select
        col.Name [Collection]
        ,td_ll.numValue / 60.0 / 24.0 [LoanDays]
     from
        PolarisTransactions.Polaris.TransactionHeaders th
     join
        PolarisTransactions.Polaris.TransactionDetails td_ll on (td_ll.TransactionID = th.TransactionID and td_ll.TransactionSubTypeID = 323)
     join
        PolarisTransactions.Polaris.TransactionDetails td_item on (td_item.TransactionID = th.TransactionID and td_item.TransactionSubTypeID = 38)
     join
        Polaris.polaris.CircItemRecords cir on cir.ItemRecordID = td_item.numValue
     join
        Polaris.Polaris.Collections col on col.CollectionID = cir.AssignedCollectionID
     where
        th.TransactionTypeID = 6002
     and
        isnull(td_ll.numValue,0) between 1000 and 1000000 -- adjust these numbers to eliminate outliers but also further skew number of items downwards
     and
        th.TranClientDate between @startDate and @endDate
) d
group by
    d.Collection