Average Number of Patrons Waiting On Holds Per Week For a Given BibliographicRecordID

Clearinghouse Repository Polaris Repository
, ,
1

This query takes a given BibliographicRecordID and produces an average number of holds created per week to let you know how many people, on average, are waiting in the request queue for that bib record.

You’re going to run into a couple of issues with this kind of query. The first is churn. Because a hold will be placed and another hold is filled and another hold is placed and so on. It’s hard to know how many are on the list at any given time because, who knows? Maybe three people placed a hold that day and two got their holds filled. So at the start of the day, three people were waiting but by the end, only one person was. So I’m going to track only the number of holds created, but you could modify this to take other actions into account.

Second, you run into a Scottish coastline problem as to how far down to you want to go for your average? I figured a week might be good? I dunno, seemed right. But this could be modified to suit for a month or something other.

You get two things at the end of the query:

A lifetime average of holds per week since the bib record was created.

A week by week count of holds since the bib record was created.

-- Drop your BibliographicRecordID below.
DECLARE @BibID INT = 2198702;

-- Get the bibliographic record creation date. No need to search for activity before this date.
DECLARE @BibCreationDate DATETIME = (
    SELECT CreationDate
    FROM Polaris.Polaris.BibliographicRecords WITH (NOLOCK)
    WHERE BibliographicRecordID = @BibID);

-- This sets your end date, normally from the creation date to today.
DECLARE @Today DATETIME = GETDATE(); 

-- Date variables slow execution, this helps speed things along.
CREATE TABLE #TempTimeSpan (
    StartTime DATETIME,
    EndTime DATETIME);

-- Populate the #TempTimeSpan table.
INSERT INTO #TempTimeSpan(StartTime, EndTime) VALUES (@BibCreationDate, @Today);

-- Get only the relevant data and act upon it.
CREATE TABLE #TempHoldDates (
    TransactionID INT,
    BibliographicRecordID INT,
    HoldRequestDate DATETIME
);

-- Populate #TempHoldDates
INSERT INTO #TempHoldDates

SELECT
    th.TransactionID,
    bibid.numValue,
    th.TranClientDate
FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get BibliographicRecordID from TransactionDetails
    PolarisTransactions.Polaris.TransactionDetails bibid WITH (NOLOCK)
    ON (th.TransactionID = bibid.TransactionID AND bibid.TransactionSubTypeID = 36)
WHERE
    bibid.numValue = @BibID
AND -- Hold request created, modify as needed
    th.TransactionTypeID = 6005
AND -- Pull dates from #TempTimeSpan
    th.TranClientDate >= (SELECT MAX(StartTime) FROM #TempTimeSpan)
AND
    th.TranClientDate < (SELECT MAX(Endtime) FROM #TempTimeSpan);

-- Deliver a lifetime average of holds per week
SELECT 
    AVG(holds_per_week) AS [Average Holds Per Week]
FROM (
SELECT 
    DATEPART(week, HoldRequestDate) AS week_number,
    COUNT(*) AS holds_per_week
FROM 
    #TempHoldDates
GROUP BY 
    DATEPART(week, HoldRequestDate)
) AS weekly_counts;

-- Deliever a week by week count of holds created.
SELECT 
    MIN(HoldRequestDate) AS [First Day of Week],
    COUNT(*) AS [Holds This Week]
FROM 
    #TempHoldDates
GROUP BY 
    DATEPART(week, HoldRequestDate)
ORDER BY
    MIN(HoldRequestDate) ASC;

-- Tidy up
DROP TABLE #TempHoldDates;
DROP TABLE #TempTimeSpan;
2 Likes