Determine How Long Items Sit on Hold Pickup Shelves
This query pulls a list of items that were placed on hold and subsequently became held. Then it pulls a list of those held items that were actually checked out. Finally, it compares the dates of those two events to get you a count of how long items stayed on the hold pickup shelves.
Pulled from the Supportal - SQL by jjack@aclib.us
DECLARE @From DATETIME;
DECLARE @Through DATETIME;
SET @From = '2021-01-01'; -- Set your start date
SET @Through = '2021-09-21'; -- Set your final date
SELECT
td.numValue AS 'HoldRequestID',
MAX(th.TranClientDate) AS 'Held'
INTO
#tmpHeldDate
FROM
PolarisTransactions..TransactionHeaders th (NOLOCK)
JOIN
PolarisTransactions..TransactionDetails td (NOLOCK) ON (td.TransactionID = th.TransactionID) AND (td.TransactionSubTypeID = '233') --HoldRequestID
WHERE
th.TransactionTypeID = '6006'
AND
th.TranClientDate BETWEEN @From AND @Through + ' 11:59:59 pm'
GROUP BY
td.numValue
SELECT
td.numValue AS 'HoldRequestID',
th.TranClientDate AS 'Filled'
INTO
#tmpFilledDate
FROM
PolarisTransactions..TransactionHeaders th (NOLOCK)
JOIN
PolarisTransactions..TransactionDetails td (NOLOCK) ON (td.TransactionID = th.TransactionID) AND (td.TransactionSubTypeID = '233') --HoldRequestID
WHERE
th.TransactionTypeID = '6039'
AND
th.TranClientDate BETWEEN @From AND @Through + ' 11:59:59 pm'
SELECT
datediff(day,thd.Held,tfd.Filled) AS 'Days held', count(datediff(day,thd.Held,tfd.Filled)) AS '# held'
FROM
#tmpHeldDate thd with (NOLOCK)
JOIN
#tmpFilledDate tfd with (NOLOCK)
ON
thd.HoldRequestID = tfd.HoldRequestID
GROUP BY
datediff(day,thd.Held,tfd.Filled)
ORDER BY
datediff(day,thd.Held,tfd.Filled)
DROP TABLE
#tmpHeldDate
DROP TABLE
#tmpFilledDate