Average Days Held by Material Type

Clearinghouse Repository Polaris Repository
,
1

Average Days Held by Material Type

This was a contribution from another source but I’m not sure who. If this is your code, let me know! It delivers the average number of days items are held per material type.

declare @startDate date = '2019-07-01 00:00:00.000'
declare @endDate date = '2021-08-31 23:59:59.999'

select
    d.MaterialType
    ,avg(d.LoanDays) [AvgLoanDays]
    ,min(d.LoanDays) [MinLoanDays]
    ,max(d.LoanDays) [MaxLoandays]
    ,count(*) [NumItemsNotEntirelyAccurate]
from 
    (select
        mt.Description [MaterialType]
        ,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.MaterialTypes mt on mt.MaterialTypeID = cir.MaterialTypeID
     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.MaterialType