# Average Days Held By Collection

**URL:** https://forum.innovativeusers.org/t/average-days-held-by-collection/1192
**Category:** Polaris Repository
**Tags:** cp-migration, polaris-holds
**Created:** [June 2, 2023, 8:59pm UTC](https://forum.innovativeusers.org/t/average-days-held-by-collection/1192 "2023-06-02T20:59:54Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![system](https://forum.innovativeusers.org/uploads/db7787/original/2X/8/8519ee8eef6492f310ea87d4ce482d931f01eef2.png) [@system](https://forum.innovativeusers.org/u/system)
#### Post date: [June 2, 2023, 8:59pm UTC](https://forum.innovativeusers.org/t/average-days-held-by-collection/1192/1 "2023-06-02T20:59:54Z")

</div>

# Average Days Held By Collection

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

```sql
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

```