Average Days Held By Collection

Clearinghouse Repository Polaris Repository
,
1

Average Days Held By Collection

This query offers a look at the average number of days items within given collections are held.

SELECT
    td.numValue AS 'HoldRequestID',
    MAX(th.TransactionDate) AS 'Held',
    col.Name 
INTO
    #tmpHeldDate 
FROM
    PolarisTransactions.Polaris.TransactionHeaders th (NOLOCK) 
JOIN
    PolarisTransactions.Polaris.TransactionDetails td (NOLOCK) 
ON
    (td.TransactionID = th.TransactionID) AND (td.TransactionSubTypeID = '233') --HoldRequestID
JOIN
    PolarisTransactions.Polaris.TransactionDetails tdColl (NOLOCK)  ON (tdColl.TransactionID = th.TransactionID) AND (tdColl.TransactionSubTypeID = '61') --CollectionID
JOIN
    Polaris.Polaris.Collections col with (NOLOCK) 
ON
    tdColl.numValue = CollectionID 
WHERE
    th.TransactionTypeID = '6006' --hold held
AND
    col.CollectionID IN (SELECT CollectionID FROM Polaris.Polaris.Collections WITH (NOLOCK))
GROUP BY
    td.numValue,
    col.Name

SELECT
    td.numValue AS 'HoldRequestID',
    th.TransactionDate AS 'Created'
INTO
    #tmpCreatedDate 
FROM
    PolarisTransactions.Polaris.TransactionHeaders th (NOLOCK) 
JOIN
    PolarisTransactions.Polaris.TransactionDetails td (NOLOCK) ON (td.TransactionID = th.TransactionID) AND (td.TransactionSubTypeID = '233') --HoldRequestID
WHERE
    th.TransactionTypeID = '6005' --hold created
AND
    th.TransactionDate BETWEEN '2021-09-01 00:00:00.000' AND '2022-05-31 23:59:59.999'

SELECT
    thd.Name AS 'Description', AVG(datediff(day,tfd.Created,thd.Held)) AS 'Days held'
FROM
    #tmpHeldDate thd with (NOLOCK) 
JOIN
    #tmpCreatedDate tfd with (NOLOCK) ON thd.HoldRequestID = tfd.HoldRequestID
GROUP BY
    thd.Name
ORDER BY
    thd.Name

--Tidy up
DROP TABLE #tmpHeldDate 
DROP TABLE #tmpCreatedDate